CODEWITHSUNDEEP
excelvba
user3224542
user3224542

Reputation: 91

VBA automation of Excel leaves a process in memory after Quit

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

Upvotes: 9

Views: 27407

Answers (5)

Q---ten
Q---ten

Reputation: 2526

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

Upvotes: 2

TomazVDSN
TomazVDSN

Reputation: 1

Need to use only:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Excel.Application.Quit

End Sub

Upvotes: -2

lonestorm
lonestorm

Reputation: 380

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")

Upvotes: 1

Dave Excel
Dave Excel

Reputation: 351

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.

Upvotes: 2

Uri Goren
Uri Goren

Reputation: 13682

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

Upvotes: 0

Related Questions