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

Answer 1

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.