How to close Excel so EXCEL.EXE stops running in task manager

Question

I have an access database that opens an excel file, updates information in it and then runs a macro several times then closes the excel file. The problem is, the EXCEL.EXE instances are still active even once the code is done executing and Access has been closed. I have tried all of the recommendations from this post on both the Access side and as a macro in the excel file that is called after the main macro is run and no matter what the process is left running. I have double checked and there is nothing else opening a reference to Excel anywhere else in the code that runs and the excel macro does not open a new instance of Excel either.

I'm using Excel 2016 and Access 2007 (due to company policy we aren't allowed to upgrade to a newer version of Access).

The original code I'm using to open the excel file, run the macro and close the excel application is as follows:

Dim XL As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set XL = New Excel.Application
XL.Visible = False
XL.DisplayAlerts = False

Set WB = XL.Workbooks.Open(Exporter)
Set WS = WB.Sheets(1)

For i = 0 to 9
    '-Generate data to place in excel sheet here-
    WS.Range("A1") = SomeData
    WS.Range("B1") = SomeOtherData
    XL.Run "Excel Macro"
Next i

Set WS = Nothing
WB.Close False
Set WB = Nothing
XL.Quit
Set XL = Nothing

This looks ok, so I assume it's in the Excel macro. Try changing the code in `“Excel Macro”` to something trivial (or nothing). And please don't use typographic quotes in code. — Andre, Feb 09 '17 at 17:41
I made a sub that just printed a comment with `debug.print` and terminated and looking in the task manager the new instance of EXCEL.EXE does not terminate. — 110SidedHexagon, Feb 09 '17 at 17:50
Crazy idea: keep a reference to **all** objects you're accessing (including the `Sheets` collection, and every accessed `Range` object), and `Set` them to `Nothing` in your cleanup code. That *shouldn't* be necessary, given this is VBA code - but in COM interop code that's how you'd get a lingering ghost process to terminate properly. — Mathieu Guindon, Feb 09 '17 at 19:31
Try running your code with `XL.Visible = True` to verify that that there are not any dialogs that are not suppressed by `XL.DisplayAlerts = False`. — TnTinMn, Feb 09 '17 at 20:24
I'm with @TnTinMn - comment the `.Visible` and `.DisplayAlerts` lines until you get it sorted. There's something in your Excel Macro that's hanging - those lines are hiding something from you. — FreeMan, Feb 09 '17 at 20:55

score 1 · Answer 1 · edited May 23 '17 at 12:24

It looks like it wasn't actually an issue with the code. My company has a watermark COM Add-In for Excel that loads automatically when Excel is opened, and apparently having COM Add-In loaded in the instance of Excel created by my code is not allowed to stop execution as long as the Add-In is loaded as per the discussion in this SO question. I had to turn off the COM Add-In manually due to not having the permissions to do it via VBA, but I suspect that the following code should turn off any offending COM Add-Ins given you have the proper permissions.

For i = 1 To XL.COMAddIns.Count
    XL.COMAddIns(j).Connect = False
Next i

How to close Excel so EXCEL.EXE stops running in task manager

1 Answers1