Quitting an instance of Excel

[ExcelChampion]

I have a macro that creates an instance of Excel, does some stuff and then ends, leaving the workbook open and visible. If I then manually close workbook, the instance of Excel still remains running in the background. I had Dim'd the instance of Excel locally originally, and no instances of Excel remained open. But, I had to make the instance of Excel Public, and that's when the issue started.

At the end of the macro, I use: xlApp.Quit, where xlApp is the public instance of Excel.

Anyone run across this and have a solution?

Thanks.

 

[Norie]

Todd

There could be various reasons for this, could you post the code and tell us where you are running the code from, and if it's Excel VBA why you are using a separate instance?

 

[ExcelChampion]

Norie! You have helped me almost exclusively over the years. You're like my guardian angel. Thank you so much...again.

First, I don't know what I was thinking, but the Quit command I mentioned above only fires when an error occurs, so that's irrelevant to my post above.

The code actually runs out of Access, and its quite big. Extensive formatting-code has bloated it...eventually I'll create an Excel template as a starting point to get rid of the bloat.

But anyway, this is essentially the code:

CurrProject_Path = Left(Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("tbl_Inhouse_Tooling").Connect, 11), Len(Mid(DBEngine.Workspaces(0).Databases (0).TableDefs("tbl_Inhouse_Tooling").Connect, 11)) - 26) 'This is a split project, and this line gets the path to the data tables
    
xlFileTemp = Replace(CurrProject_Path, "Templates", "") & "\Temp\Temp_" & Format(Now, "#.#####") & ".xlsx" 'This is really not a file, but a file path

Set xlApp = CreateObject("Excel.Application") 'This creates the instance of Excel...remember, xlApp is a Public variable

With xlApp
        .Workbooks.Add
        .WindowState = xlMaximized
        .ActiveWorkbook.SaveAs FileName:=xlFileTemp
End With

myTempWkbk = xlApp.ActiveWorkbook.Name

'Do a whole lot of stuff in Excel, including the following, which now I wonder if its the culprit:

xlFile = CurrProject_Path & "\Temp\RFQ " & Forms("RFQ FORM").Controls("RFQNUM") & " Materials_" & Format(Now, "#.#####") & ".xlsx"
DoCmd.SetWarnings False
DoCmd.OutputTo acOutputQuery, "qry_Material_BoM_Eng_Crosstab", "ExcelWorkbook(*.xlsx)", xlFile, False, "", , acExportQualityPrint
xlApp.Workbooks.Open xlFile, UpdateLinks:=False

'Do more stuff

xlApp.ActiveWorkbook.Save
xlApp.Visible = True
Exit Sub

I've tried making the instance of Excel running in the background visible, but either I'm doing it wrong, or, there's just nothing to show. I just find it strange that when this code ends, I have an open workbook. If I look in Task Manager, there's only one instance of Excel open. When I close the workbook, that instance of Excel remains. I can't wrap my head around that if I close the workbook, that should kill the instance of Excel...right?

 

[Norie]

Todd

I've only had a quick look at the code you posted, and it seems fine - everything seems to be referenced properly and dealt with properly.

The stuff that could be causing the problem might be the 'whole lot of stuff' in the middle.

Actually, in the code you posted I'm not seeing where you are closing down Excel.

For that I would expect to see something like this.

xlApp.Quit

 

[ExcelChampion]

Actually, in the code you posted I'm not seeing where you are closing down Excel.

That's right. Access is essentially creating a report for the user, so I don't want the macro to close Excel. It creates the report, and then shows it to the user. This instance of Excel is only closed when the user manually closes it. But that's the problem. The user closes Excel, but it continues to run in task manager.

Another odd thing is that the macro can be run again and again and again, and only one instance of Excel is showing in the background. In other words, its not like multiple instances are stacking up in task manager.

For grins, I'm going to see if xlApp.Quit actually quits the instance...

Thanks, Norie. I'll review the code more and try a couple of things. I'll post back if anything interesting happens.

 

[ExcelChampion]

Ok, this is weird. This code should close the instance of Excel, but it does not.

Sub Close_Excel()
Dim xlApp As Excel.Application
    Set xlApp = GetObject(, "Excel.Application")
        xlApp.Visible = True
        xlApp.Quit
End Sub

It does show the instance, and the quit command closes the workbook, but its still running in task manager. I have no idea what code to use, if it exists, that will kill it in task manager. Very odd.

 

[Norie]

Todd

Have you tried sauntering through the code and checking, once in a while, Task Manager to see how many instances of Excel are running?