VBA automation of Excel leaves a process in memory after Quit

Question

I have seen a lot of suggestions for this problem, and I have tried them all, but none seem to work. The VBA code is in a non-Microsoft product (SAP Business Objects, which might be the problem). I create an Excel object:

Set oExcel = CreateObject("Excel.Application")

Load the contents from column 1 of one of the WorkSheets in a particular workbook, then close Excel. Each time, it leaves a process in memory, taking up 5+ mb of memory.

I tried making the oExcel object visible, so that at least I could kill it without resorting to the Task Manager, but when I call Quit, the UI quits, and still leaves the process.

Every time I run the code, it creates a new process. So I tried to reuse any existing Excel processes by calling

Set m_oExcel = GetObject(, "Excel.Application")

and only creating it if that call returns nothing,

That did not proliferate the processes, but the single process grew by 5+ mb each time, so essentially the same problem.

In each case, I close the workbook I opened and set DisplayAlerts to False before quitting:

m_oBook.Close SaveChanges:=False
m_oExcel.DisplayAlerts = False
m_oExcel.Quit

This bit of code has been in use for at least five years, but this problem did not crop up until we moved to Windows 7.

Here is the full code in case it helps. Note all the Excel objects are module level variables ("m_" prefix) per one suggestion, and I have used the "one-dot" rule per another suggestion. I also tried using generic objects (i.e. late bound) but that did not resolve the problem either:

Private Function GetVariablesFromXLS(ByVal sFile As String) As Boolean
    On Error GoTo SubError

    If Dir(sFile) = "" Then
        MsgBox "File '" & sFile & "' does not exist.  " & _
               "The Agent and Account lists have not been updated."
    Else
        Set m_oExcel = CreateObject("Excel.Application")
        Set m_oBooks = m_oExcel.Workbooks
        Set m_oBook = m_oBooks.Open(sFile)

        ThisDocument.Variables("Agent(s)").Value = DelimitedList("Agents")
        ThisDocument.Variables("Account(s)").Value = DelimitedList("Accounts")
    End If

    GetVariablesFromXLS = True

SubExit:

    On Error GoTo ResumeNext
    m_oBook.Close SaveChanges:=False
    Set m_oBook = Nothing
    Set m_oBooks = Nothing

    m_oExcel.DisplayAlerts = False
    m_oExcel.Quit

    Set m_oExcel = Nothing

    Exit Function

SubError:
    MsgBox Err.Description
    GetVariablesFromXLS = False
    Resume SubExit

ResumeNext:
    MsgBox Err.Description
    GetVariablesFromXLS = False
    Resume Next

End Function

I think the Task Manager is notoriously unreliable for this sort of thing... I think there have been other Q's here on a similar topic and the consensus is that TM is incorrect. Let me see if I can confirm that. — David Zemens, Jan 22 '14 at 18:34
Try to change `m_oBook.Close SaveChanges:=False` to `m_oBook.Saved = True` — Dmitry Pavliv, Jan 22 '14 at 18:42
hmmmm I'm playing with this and `Set m_oExcel = Nothing` releases it from Task Manager *every time*; even if I don't do `m_oExcel.Quit`. In fact, even if I don't set it to Nothing, Task Manager removes the process after run-time. — David Zemens, Jan 22 '14 at 18:48
`Set oExcel = CreateObject("Excel.Application")` <- doesn't create an Excel.Exe process in the task manager. Try stepping through your code to see when the instance actually gets started and then look at your code while stepping through and figure out at which point you want to kill the instance (*in task manager*). Once you know which line doesn't do what you want it to do then edit your post with just relevant code. — , Jan 23 '14 at 08:17
Are you sure you are looking at Processes in Task Manager and not Applications? I am stepping through it and immediately on the call to CreateObject() it appears in Processes. — user3224542, Jan 23 '14 at 16:45
Regarding m_oBook.Saved = True vs. SaveChanges:=False, the workbook is not changed, so either is superfluous. Watching the Process in Task Manager I can see that in both cases the size of the process eventually changes from ~50mb to ~5, so the .Close is having some effect. A few more details might help: I am running Office 2010 on a Win7 Enterprise - 64 bit OS. The process reads "EXCEL.EXE *32". — user3224542, Jan 23 '14 at 16:53
What application are people using to test this? As I noted, this is running in a non-Microsoft application (SAP Business Objects). If you are testing from an Office app you might inadvertently be avoiding the true problem. — user3224542, Jan 23 '14 at 16:55
I dont see the part when you are loading in the column? Perhaps, you have an unitentional global reference - see [here](http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/) — brettdj, Jan 25 '14 at 09:42
The columns are loaded in the function DelimitedList(tabName), which refers to the module level variable m_oBook. Originally I had passed the worksheet object, but changed to module level variables so that there would only ever be one reference to any Excel object. One of the sugestions had been to do it this way to avoid "implicit" references that would leave a reference count >1 on exit. Didn't work. — user3224542, Jan 27 '14 at 14:22
Excel changed some stuff recently (I think in Office 2010?) with regard to multiple documents in the same instance. I wonder whether .Quit is just closing the current document but not the session itself. Might be worth trying to call .Quit a few times, just to see whether that might be the problem. — Chris Rae, Mar 06 '14 at 11:09

score 2 · Answer 1 · answered Dec 14 '14 at 04:38

2

Most times this happens because Excel is keeping a COM Add-in open. Try using the link below for help on removing the COM Add-in.

Add or remove add-ins

I find particular comfort in the note:

Note This removes the add-in from memory but keeps its name in the list of available add-ins. It does not delete the add-in from your computer.

answered Dec 14 '14 at 04:38

Dave Excel

351
2
5

Use `oExcel.COMAddIns([Index]).Connect = False` to turn off any COM add-ins you may have active. This solved this issue for me. – 110SidedHexagon Feb 09 '17 at 19:54

score 2 · Answer 2 · answered Oct 11 '17 at 05:19

2

Adding an answer based on David Zemens comment. Works for me.

m_oExcel.Quit            '<- Still in Task Manager after this line
Set m_oExcel = Nothing   '<- Gone after this line

answered Oct 11 '17 at 05:19

Q---ten

2,168
1
13
17

Worked for me as well, thanks for the hotfix Q---ten – Riley Carney Aug 27 '18 at 21:16

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

This question has already been answered by Acantud in response to a subsequent post: https://stackoverflow.com/questions/25147242 Fully qualify your references to objects within the Excel workbook you open to avoid creating orphaned processes in the task manager. In this case, the solution is to prefix DelimitedList with m_oBook, such as

ThisDocument.Variables("Agent(s)").Value = m_oBook.DelimitedList("Agents")

score 0 · Answer 4 · answered Feb 17 '14 at 20:35

Though this isn't supposed to happen, you could send excel a "WindowClose" message in order to force close.

You'll be needing these API functions

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess As Long, ByVal uExitCode As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As Long, lpdwProcessId As Long) As Long
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

And it should look something like this:

// First, get the handle
hWindow = FindWindow(vbNullString, "Excel")
//Get proccess ID
GetWindowThreadProcessId(hWindow, ProcessValueID)
//Kill the process
ProcessValue = OpenProcess(PROCESS_ALL_ACCESS, CLng(0), ProcessValueID)
TerminateProcess(ProcessValue, CLng(0))
CloseHandle ProcessValueID

That didn't work either - the "EXCEL.EXE *32" process is still in the process list after everything completes - inlcuding the application hosting VBA. I had to change the "Public" on FindWindow to "Private" since VBA complained about a public declaration in a module, and I changed PROCESS_ALL_ACCESS to PROCESS_TERMINATE because I could not find the definition for PROCESS_ALL_ACCESS. Since all I am doing is terminating, I assume this would not make a difference. — user3224542, Feb 18 '14 at 21:46

score -2 · Answer 5 · answered Jun 24 '16 at 05:10

-2

Need to use only:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Excel.Application.Quit

End Sub

answered Jun 24 '16 at 05:10

TomazVDSN

1

VBA automation of Excel leaves a process in memory after Quit

5 Answers5

Linked