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]

 

[strongm]

>Yes. The code at the top is what I was actually running

Ok, well if I open a completely new Access database (in my case Access 2010) and copy JUST the code you posted into a module and run it I don't get the problem that you are suffering. Which is why I don't think that it is this code by itself that is the cause of the problem.

let's see ... does your autoexec macro do anything at all apart from run the sub in question? Is it for example running the sub via a proxie function (as you describe you sometimes do in your last post), and if so does the proxie function do anything apart from run the sub?

 

[merlizbet]

Remember, too, that I said I tested closing the 2010 ver on my desktop, then opened it in a 2013 ver on a separate laptop (it's on our network, so it is *the* exact file I'm opening in each), and I, also, did not get the same problem. I did, also, see a web post from someone saying they copied their 2010 app that was not working to a different PC running 2010, and it then worked for them. That's why I figured a reboot might fix my problem; but no. I have another laptop available with 2010, and I could try opening it on that to see what happens. I could also try importing all the code to a new db on my same 2010 desktop.

All of that aside, yes, there is plenty of other stuff happening in my autoexec macro (I'll post that below). I haven't run the "test" sub after running *only* this routine (that might be useful info), but the number of Excel instances issue appearing in the "Process" window happens if I hold down the Shift key to bypass the autoexec macro on startup and just manually run this one routine (from a separate macro that does nothing but run this code).

Here's what my autoexec macro looks like. Note that where I have some functions with the name including "Excel", those are not actually opening Excel to do the looking, but rather are looking at the data I've already imported from Excel into 2 Access tables. I had to import those 2 Excel files to convert their data to all text for some checking purposes I couldn't figure out how to make happen otherwise. I might be inclined to throw some suspicion on the RunSavedImportExport, except that the problem happens even when I bypass the autoexec macro.

Here's the autoexec macro:

 

[strongm]

>I, also, did not get the same problem

Yes. And the point I am making is that it is not this specific bit of code that is causing the problem. And it isn't a problem with the PC.

So it is either some other bit of code that is running (perhaps unexpectedly - e.g. an event that is triggered) or something related to the configuration of your specific instance of Access.