Excel VBA automation leaves process in memory after exiting

Question

I saw a lot of suggestions on this issue, and I tried all of them, but no one seems to work. The VBA code is in a product other than Microsoft (SAP Business Objects, which may be a problem). I create an Excel object:

Set oExcel = CreateObject("Excel.Application")

Download the contents from column 1 of one of the worksheets into a separate workbook, then close Excel. Each time he leaves the process in memory, occupying 5 mb of memory.

I tried to make the oExcel object visible, so at least I could kill it without resorting to the task manager, but when I invoke Quit, the user interface exits and still leaves the process.

Each 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 this call returns nothing,

This did not propagate the processes, but each process grew by 5+ mb each time, so essentially the same problem.

In each case, I close the open book and set DisplayAlerts to False before exiting:

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

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

Here is the complete code if it helps. Note that all Excel objects are module level variables (prefix "m_") per sentence, and I used the "one point" rule for another sentence. I also tried using shared objects (i.e. Late restrictions), but this also did not help solve the problem:

 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

In most cases, this is because Excel keeps the COM add-in open. Try the link below for help on removing the COM add-in.

Add or remove add-ons

I found particular comfort in a note:

Note. This removes the add-in from memory, but stores its name in the list of available add-ons. It does not remove the add-in from your computer.

Answer 2

This question has already been answered by Acantud in response to the following publication: https://stackoverflow.com/a/168268/# Fully qualify your references to objects in the Excel workbook that you open to avoid creating lost processes in the task manager. In this case, the solution has the DelimitedList prefix with m_oBook , for example

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

Answer 3

Although this should not happen, you can send excel a "WindowClose" message to force close.

You will need 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 

Answer 4

Adding an answer based on a comment by David Zemens. It works for me.

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

Answer 5

Only use:

 Private Sub Workbook_BeforeClose(Cancel As Boolean) Excel.Application.Quit End Sub