Excel does not quit in Task Manager from VBA

[merlizbet]

Hello good VBA folks,

I've searched/reviewed this forum and many other pages on the web. I find lots of people who have had the same issue, but every fix I've seen that takes care of someone's problem like this does not seem to work for me. My code does what I need for it to do (make the top line of each spreadsheet be the field names) and I can use it as-is, but I just can't make Excel close in Task Manager. I think I've tried about every web suggestion I've found, to no avail. If anyone has time to review my code and offer suggestions for me to try, I'm very interested to figure this out.

Thanks!

Notes:
[ul]
[li]Because this Access app is going between users of Access2010 and Access2013, based on a web find, I change to "late binding" (i.e., Dimming as Object rather than Excel.Application) to eliminate a Library Reference issue between the 2 versions.[/li]
[li]On thing I have determined is that if I run the code to Set xlApp = New Excel.Application and then jump immediately to xlApp.Quit, when it runs past Set xlApp = Nothing, this instance of Excel closes in Task Manager. As suggested by another web article, this prompted me to research and insert code to test to see if one of the Workbooks was still open (note that code in yellow below); but this code indicated no open Workbook(s).[/li]
[li]Based on another web article, I also discovered that, if I don't run xlApp.Quit, Excel (with no Workbook displaying) remains visible. When I then manually close Excel, *this* instance of Excel disappears from Task Manager (the desired effect except that I want to handle it with VBA).[/li]
[li]I also found a web article saying Excel would disappear from Task Manager when MSAccess was closed, but I did *not* find that to be the case.[/li]
[/ul]

Key:
I have added and moved things around and around trying to get Excel to close via VBA. To help distinguish code from comments below for your review:
[ul]
[li]Currently running code is in black.[/li]
[li]Comments are in [highlight #8AE234]green[/highlight] or [highlight #FCE94F]yellow[/highlight] highlight.[/li]
[li]Code that has been tried and commented out is in brown.[/li]
[/ul]

[pre]
Public Sub sRemovePSQCountRow()
[highlight #8AE234]'To bypass Excel 14.0 vs 15.0 Ref Lib between 2010 and 2013 vers of Access...
'

http://www.experts-exchange.com/Database/MS_Access/Q_28228961.html

'LATE BINDING:

http://support.microsoft.com/kb/245115

[/highlight]

Dim strExcelFilePath As String
'Dim xlApp As Excel.Application
'Dim xlWB As Excel.Workbook
'Dim xlSheet As Excel.Worksheet
Dim xlApp As Object [highlight #8AE234]'To bypass Excel 14.0 vs 15.0 Ref Lib between 2010 and 2013 vers of Access...[/highlight]
Dim xlWB As Object [highlight #8AE234]'Ditto[/highlight]
Dim xlSheet As Object [highlight #8AE234]'Ditto[/highlight]
Dim intPass As Integer
Dim sKill As String
Dim intWBCnt As Integer

[highlight #8AE234]'We need to delete the count row in all of the fresh PSQ data Excel files.
'We'll do that by looping through the row-delete process for each file.[/highlight]

[highlight #8AE234]'First we open Excel
'Either of the two Sets seems to open Excel. What I can't do is get it closed at the end.[/highlight]
'Set xlApp = CreateObject("Excel.Application")
Set xlApp = New Excel.Application
'GoTo CompleteProcess:
xlApp.Visible = True

[highlight #8AE234]'Loop through the process for all files (chg 4 to however many files you want to loop through)[/highlight]
For intPass = 1 To 4
If intPass = 1 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_CLSLVL_AY.xls"
ElseIf intPass = 2 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_CLSLVL_SS.xls"
ElseIf intPass = 3 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_AWARDS.xls"
ElseIf intPass = 4 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_UNAPPLIED.xls"
End If

[highlight #8AE234]'We first need to make sure something didn't go wrong with the Cybermation process to[/highlight]
[highlight #8AE234]' run each PSQ so that the PSQ Excel doesn't exist. If it did go wrong, we'll[/highlight]
[highlight #8AE234]' present a message to investigate and then get out of this sub.[/highlight]
If Len(Dir(strExcelFilePath)) = 0 Then
MsgBox strExcelFilePath & " is missing! Note the missing file name and " & _
"contact Mary E. to investigate.", vbOKOnly, ">>>Problem!<<<"
GoTo CompleteProcess
Else

[highlight #8AE234]'If the Excel file exists, we'll move on with deleting the count row.[/highlight]

'Set xlWB = xlApp.Workbooks.Open(strExcelFilePath, True, False)
Set xlWB = xlApp.Workbooks.Open(strExcelFilePath, , False)

[highlight #8AE234]'Appears xlSheet is unnecessary if just looking at Sheet 1. Leave this so I will know how to do other than Sheet 1.[/highlight]
Set xlSheet = xlWB.Worksheets(1)

[highlight #8AE234]'Test to see if 1st row looks like the count row before deleting it[/highlight]
If Not IsEmpty(xlSheet.Range("A1")) And (Not IsEmpty(xlSheet.Range("B1")) And xlSheet.Range("B1").Value > -1) And IsEmpty(xlSheet.Range("C1")) And Not IsEmpty(xlSheet.Range("A2")) Then
xlSheet.Rows(1).EntireRow.Delete
End If

[highlight #8AE234]'Close and save the .xls file regardless of whether or not we made changes[/highlight]
xlWB.Close True
Set xlSheet = Nothing
Set xlWB = Nothing

[highlight #8AE234]'If we've handled the last PSQ Excel file, close Excel and we're done. If not,
' increment the Pass number and do the process for the next Excel file.[/highlight]
End If
Next intPass

CompleteProcess:

[highlight #FCE94F]'Attempting to figure out why Task Mgr won't let go of Excel.
'This indicates xlWB has no Fullname (i.e., no workbooks are still open. Then why won't Excel end??)[/highlight]
intWBCnt = 0
For Each xlWB In xlApp.Workbooks
intWBCnt = intWBCnt + 1
'MsgBox xlWB.FullName
xlWB.Save
xlWB.Close
Next xlWB
'MsgBox intWBCnt & " workbooks are open."

[highlight #FCE94F]'Quit Excel <-- This doesn't seem to work. Task Mgr still shows Excel running.[/highlight]
xlApp.Quit

Set xlApp = Nothing

[highlight #FCE94F]'Kill all instances of Excel still running. It kills Excel, but can't use this as it might kill unrelated spreadsheets that the user wants left open.[/highlight]
'sKill = "TASKKILL /F /IM excel.exe"
'Shell sKill, vbHide

End Sub

[/pre]

 

[Andrzejek]

I just tried this code in VB6 and in VBA in Word (2010) with no other Excel running:

Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Quit
Set xlApp = Nothing

and watched Windows Task Manager (Ctrl-Shift-Esc)

Excel shows up and then is gone.
Must be something peculiar with Access...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[merlizbet]

Thanks you for your quick reply.

You will note in my code that is commented out that I did try
'Set xlApp = CreateObject("Excel.Application")
Set xlApp = New Excel.Application

That was my original code and, since it was not working, I just today found someone had solved their same problem with Set xlApp = New Excel.Application. But neither code seems to do the trick for me.

As noted in my original question above, when I bypass the code that opens the Workbooks (the main purpose of this routine) and only run the code similar to what you tried, it *does* close Excel for me, also. But when I include the code for the Workbooks, Excel no longer closes. It's as though something definitely has it hung open, but I surely can't figure out what that might be.

 

[Andrzejek]

So I added a little more to the code:

Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlWB = xlApp.Workbooks.Open("C:\Excel\ABCD.xls", , False)

Set xlSheet = xlWB.Worksheets(1)
xlSheet.Rows(1).EntireRow.Delete

xlWB.Close True
Set xlSheet = Nothing

Set xlWB = Nothing

xlApp.Quit
Set xlApp = Nothing

Code starts Excel, opens workbook, opens sheet1, deletes first row, and it still closes Excel just fine.

What I am trying to do is to keep adding a little bit at the time to the code to find out when/what makes Excel stay in Task Manager.

You could try the same in brand new Access. Just keep adding steps and test it.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[merlizbet]

What ver of Access are you using? I'm creating in 2010 for a user in 2013 (hence the late-binding method as "MISSING" Excel ref lib was causing a prob for me in 2010 every time it was opened in 2013). It's my 2010 that isn't working. Haven't watched it in 2013. I've done some GOTOs in various places to jump code to see when it does and when it doesn't close. Basically, after it entered the file open code the first time, Excel didn't close.

I saw someone say they moved to a completely different PC with their same non-working code and then it work. That would be useless if it only worked on certain PCs, but what I haven't tried is a reboot in the event that maybe something "invisible"/unrecognized is hung up. But, even if that's the case, I can't make users reboot every time. But, I think I *will* give it a try to at least see what happens.

One other thing you might have some ideas about... Several posts elsewhere have said that *every* piece of Excel must be identified and specifically closed or the instance will hang. After I posted here, I tried to include the Ranges in that model, but I was not successful in figuring out how to do that so that it made any difference.

Thanks for continuing your efforts to help me solve this mystery!

 

[strongm]

If you are doing

>Set xlApp = New Excel.Application

then I am afraid that you are not doing late binding

 

[strongm]

Which in turn means that you msut necessarily still have a Reference to the Excel objetc library set. And therefore you may be running afoul of the global namespace that Excel likes to use. To demonstrate, start a new project, add a reference to the Excel object library and then paste the following code into a module:

[blue]Option Explicit

Public Sub Example()
    Dim myExcelApp As Object
    Dim xlWB As Object
    Dim xlSheet As Object
    Dim lp As Long
    
    Set myExcelApp = New Excel.Application 
    
    For lp = 1 To 4
        Set xlWB = myExcelApp.Workbooks.Add()
        Set xlSheet = xlWB.Worksheets(1)
        Debug.Print Workbooks.Count [green]' Workbooks is from the global namespace and causes an extra reference to Excel.Application if we have a reference to the library enabled[/green]
        Set xlSheet = Nothing
        xlWB.Close False
        Set xlWB = Nothing
    Next
    
    myExcelApp.Quit
    Set myExcelApp = Nothing
End Sub[/blue]

Run Example, and you should see your exact symptoms replicated (Excel does not disappear from Taskman)

Comment out the Debug line, and the problem will disappear.

That's the illustration.

Now, in your code change

Set myExcelApp = New Excel.Application

to

Set myExcelApp = CreateObject("Excel.Application")

and remove the reference to the Excel object library. Then try your code again, and let us know the results.

 

[merlizbet]

>If you are doing

>>Set xlApp = New Excel.Application

>then I am afraid that you are not doing late binding

That may be; I haven't opened it in 2013 then back again in 2010 since making that change. That's when the MISSING:Excel14.0 ref shows up, so could be I'd get that again with that code.

After posting here yesterday afternoon, I tried *more* stuff I found...

[pre]
Dim ref As Reference
'0 = Late Binding
#Const ExcelRef = 0
[/pre]
and changed back to...

[pre]
Set xlApp = CreateObject("Excel.Application")
[/pre]
as that is what this new bit of code was using.

I *did* just reboot and give it a try, but the instance is still running.

 

[Andrzejek]

To answer your question - I do this in VB6, no Access.

But you may try the sample of (your) code I used in my post from 3 Jul 14 8:44

Just change hard coded path [tt]C:\Excel\ABCD.xls[/tt] to some Excel file you can use.

BTW, I do NOT use a reference to Excel (late binding), hence just
[tt]
Dim xlApp As Object
...
Set xlApp = CreateObject("Excel.Application")
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[merlizbet]

I just noticed that, even though Excel is still running the Task Mgr "Processes" window, it doesn't appear in the Task Mgr "Applications" window. What do you think that means? In fact, I just closed/opened my Access app again and I watched Excel open/close in the Applications window. ??

~~~~

OK, more info to consider...
[ol 1]
[li]I closed the app in 2010 and opened it in 2013.[/li]
[li]It *did* close in the *Processes* window in 2013.[/li]
[li]I checked the 2013 Ref Lib and Excel 15.0 was checked.[/li]
[li]I closed the app in 2013 and opened it again in 2010.[/li]
[li]Excel did *not* close out of the 2010 "Processes" window (same as before).[/li]
[li]I checked the 2010 Ref Lib and Excel 14.0 was checked.[/li]
[/ol]

From another web search (not exact but similar issue): [URL unfurl="true"]http://tinyurl.com/neyxxln[/url]

[pre]
Your code is not releasing all COM references (e.g. xlApp.Workbooks.Add creates a reference
to a Workbooks object that is never released). Therefore the Excel instance does not close,
as described in this KB article, and discussed at length in numerous StackOverflow questions.

The Excel instances will eventually shut down after your process has terminated, when COM detects
that there hasn't been a ping from the client for a while.
[/pre]

In my case, Excel does not *immediately* close out the instance(s) when I close my Access app. Maybe it will eventually; I haven't closed my app and kept checking back to see.

Thoughts on this info?

 

[merlizbet]

Overlapping posts.

strongm, you say...

[pre]
Set myExcelApp = CreateObject("Excel.Application")

and remove the reference to the Excel object library. Then try your code again,
and let us know the results.
[/pre]

I've already changed my code back to CreateObject (which is what I had originally before all my web searching yesterday), but what are you suggesting I do to remove the ref? Just uncheck it in the Ref Lib group?

 

[merlizbet]

OK,
[ol 1]
[li]I uncheck Microsoft Excel 14.0 Object Library.[/li]
[li]I closed the Access app (on my 2010 PC)[/li]
[li]I closed all running Excel instances in Task Mgr[/li]
[li]I refired the app in 2010 and, unfortnately, got the same result (Excel instance still running the in Processes window).[/li]
[/ol]

I did *not* uncheck the "Microsoft Office 14.0 Object Library" as I figured Access itself might actually need something out of that.

Thoughts?

 

[strongm]

>1.I uncheck Microsoft Excel 14.0 Object Library.

Good. That eliminates that as the primary issue.

The fact that Excel starts up when you start your Access app suggests that that your app is grabbing an unexpected reference to Excel. The code yiu have provided us with so far is not doing that, so ... we need to see more of your code ...

 

[Andrzejek]

... or you can watch Taks Manager as when Excel shows up while stepping thru your code from the very beginning of your app.

I usually jump from procedure to precedure, or event to event, untill I narrow it down to the precedure. Then I try again and just step thru that recedure.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[merlizbet]

>The fact that Excel starts up when you start your Access app suggests that that your app is grabbing an unexpected reference to Excel.

Excel doesn't start until it hits the RunCode command for this particular function in my autoexec macro.

>The code yiu have provided us with so far is not doing that

I'm pretty sure this code is where the problem is. I've killed all instances of Excel and run *only* this code and this is where Excel starts and doesn't kill the instance. Also, this is the only place I need to open Excel in this app. And, remember, I said I just now watched the Processes window open and close Excel with this very same app/code when I open this app using a PC with Access 2013. Maybe we, here, should all just upgrade to 2013 which appears would make the problem moot.

I put a lot of stuff in my original post so it would have been easy to miss some things, but one thing of note is this bullet:
[ul]
[li]Based on another web article, I also discovered that, if I don't run xlApp.Quit, Excel (with no Workbook displaying) remains visible. When I then manually close Excel, *this* instance of Excel disappears from Task Manager (the desired effect except that I want to handle it with VBA).[/li]
[/ul]
...if that might suggest anything to you.

 

[merlizbet]

Andrzejek,

Using the step-through method, I can see that Excel opens in the provided code here:
[pre]
Set xlApp = CreateObject("Excel.Application")
[/pre]
If I then use a GOTO immediately after xlApp.Visible = True to jump all the Workbook code and go directly to:

[pre]
xlApp.Quit

Set xlApp = Nothing

End Sub
[/pre]
the Process instance of Excel in Task Manager closes when the step-through passes End Sub.

Seems it must either be a quirk of 2010 or 2010 is expecting me to close up some Excel piece (Workbook or Worksheet or Range or Cell) ? that I'm not properly "disposing of". Based on the Msgbox output I inserted to tell me what Workbooks are still open (as suggested by yet *another* web article), it would *seem* it is *not* the closing of a Workbook that's the problem as all of those *appear* to be closed.

I also tried Setting all the Ranges:
[pre]
Set xlWB = xlApp.Workbooks.Open(strExcelFilePath, , False)
Set xlSheet = xlWB.Worksheets(1)
Set xlRange1 = xlSheet.Range("A1")
Set xlRange2 = xlSheet.Range("B1")
Set xlRange3 = xlSheet.Range("C1")
Set xlrange4 = xlSheet.Range("A2")[/pre]
and then setting *them* to nothing:
[pre]
xlWB.Close True
Set xlRange1 = Nothing
Set xlRange2 = Nothing
Set xlRange3 = Nothing
Set xlrange4 = Nothing
Set xlSheet = Nothing
Set xlWB = Nothing
[/pre]
to no avail. I didn't see that there was code to allow me a way to do anything like "close" a Range or Worksheet.

 

[strongm]

> it doesn't appear in the Task Mgr "Applications" window. What do you think that means?

Simply means the process does not have a visible window

Is the code you have presented here the exact code you are actually running?

 

[merlizbet]

>Is the code you have presented here the exact code you are actually running?

Yes. The code at the top is what I was actually running. I copy/pasted it directly from my app to here (just with some color coding added here for y'all's reviewing). As I say, I've added and removed some things since originally posting it here (based on some things posted in this thread *and* some other web finds) (like the Set Range code I mentioned), but that original code leaves Excel running in my 2010 PC and nothing additional I've tried since originally posting it here has changed that.


Here's a thought (maybe a bad/useless one )...

I saw one or more web articles yesterday saying this was a problem for them *unless* they used Set xlApp = New Excel.Application (specifically with the "New" in front).

If I need to use late-binding and Set xlApp = New Excel.Application does not work with late-binding (if I understood you correctly in one of your earlier posts), then maybe I'm kinda "stuck between a rock and hard place"... I can't use Set xlApp = New Excel.Application with late binding and I can't switch back and forth between versions if I don't use late binding. Though I will say that, if I was *not* actually using late binding when I was using Set xlApp = New Excel.Application, I *was* still able to switch back and forth when (per an article from earlier in the week) I changed Dim xlApp As Excel.Application to Dim xlApp As Object. That seemed to allow 2010 to hang onto ref Excel14.0 even after 2013 inserted Excel15.0 when it was opened (i.e., 2010 no longer presented the MISSING ref lib error).

But, bottom line is that none of these things fixed the problem for me.

 

[strongm]

>Set xlApp = New Excel.Application does not work with late-binding (I

Correct. This is NOT late binding. No matter what the internet tries to tell you.

>a problem for them *unless* they used Set xlApp = New Excel.Application

They don't know what they are talking about

The issue is that you are - somewhere, somehow - incrementing a reference count that is then NOT decremented (and thus leaves an Excel instance open). An object will not close until it's reference count is reduced to 0 (and setting an object to nothing does not do what you think it does; it merely decrements the reference counter by 1. On the other hand, when the reference counter reaches 0 the object closes straight away, contrary to some erroneous information that you have posted in this thread - "when COM detects
that there hasn't been a ping " is rubbish) The problem is that I can't see from the code presented where that increment is happening.

 

[merlizbet]

strongm,

OK. With your last post in mind, I set about searching to see if (Part 1) I could find a way to determine the number of Excel instances running in Windows and then (Part 2) find a way to reduce that number by 1. I have found code for Part 1 here [URL unfurl="true"]http://tinyurl.com/nwcn5ul[/url].

For my test, I:
[ol 1]
[li]Copy/pasted the code found at this URL into a new VBA module, and added to that code a function just to call his "test" sub (because the only way I know to make a Sub run that isn't attached to an event is to put a call to it in a Function and then run the function from a macro).[/li]
[li]Closed all instances of Excel in the "Process" window.[/li]
[li]Ran my code above that opens and (theoretically) closes 4 Excel workbooks.[/li]
[li]Then ran the new "test" code.[/li]
[/ol]
What the "test" sub return is:
[pre] Instance_1 526876[/pre]

Then I:
[ol 1]
[li]Left this "stuck" instance of Excel running in the "Process" window.[/li]
[li]Manually opened an Excel file that I had created and named "just a test.xlsx"[/li]
[li]Then ran the new "test" code again.[/li]
[/ol]
What the "test" sub returned is:
[pre] Instance_1 526876
just a test.xlsx[/pre]

Then I:
[ol 1]
[li]Again ran my code above that opens and (theoretically) closes 4 Excel workbooks.[/li]
[li]Then ran the new "test" code.[/li]
[/ol]
What the "test" sub returned is:
[pre] Instance_1 1115882
Instance_2 526876
just a test.xlsx[/pre]

Seems this would indicate that somehow a "phantom"/not-visible Workbook is being opened with my code, being assigned a random number, and then left orphaned/unclosed.

What do you make of this?