Excel workbook closed via vba code still running in process in task manager

Question

Through excel VBA, I open the workbook and close it, leaving the Excel application. But it still works in the task manager, which prevents my add-in from working properly. Part of the code is as follows:

Private Sub btn_Click() Dim oExcel As Object Dim oBook As Object Dim oSheet As Object Dim i As Integer Dim j As Integer Sheets("Sheet1").Select Finalrow = Cells(Rows.count, 1).End(xlUp).row LastCol = Cells(1, Columns.count).End(xlToLeft).Column Set oExcel = New Excel.Application Set oBook = oExcel.Workbooks.Open(ADDIN_PATH & "\" & "hello.xls") Set oSheet = oBook.Worksheets(1) sCellName = "--Select--" oSheet.Range("A3").Value = "Name" If (ComboBox1.Value = "--Select--" Or ComboBox1.Value = "") Then MsgBox "Please Map the name field" 'Else 'oSheet.Range("B2").Value = ComboBox1.Value Exit Sub End If 'oSheet.Range("B2").Value = ComboBox1.Value oSheet.Range("B2").Value = ComboBox1.Value If (ComboBox2.Value = "") Then ComboBox2.Value = sCellName oBook.SaveAs ADDIN_PATH & "\" & "hello.xls" oBook.Close oExcel.Quit Set oExcel = Nothing MsgBox "Your current setting has been saved" SettingForm.Hide End Sub 

Answer 1

When I encounter this problem with ghostly Excel processes after creating and managing an excel application / workbook (usually from Access), the solution I found is always the same, but after performing some tests at my end, the code that you have doesn't create a ghostly process for me. In any case, the implementation of this fix should work, so here are two suggestions:

1.) You need to fully qualify each link when you create / manage a new excel application - COM monitors the number of open objects with a simple account - when you open a new excel process, it loops through 1, but if you make an unqualified link with two open (say using Cells(Rows.Count,1)... instead of oExcel.Workbooks(oBook.Name).Worksheets(oSheet.Name).Cells(oExcel.Workbooks(oBook.Name).WorkSheets(oSheet.Name).Rows.Count,1)... ), it extinguishes again, and now, even when they are all closed, the internal count of COM objects is still 1 and Excel remains open. (Obviously, you do not need to type everything every time, only when creating objects.) Therefore, I would use -

 dim thisSheet as Worksheet set thisSheet = ActiveWorkbook.Worksheets(1) thisSheet.Activate 

Even if this is not what causes the problem now, doing something like Sheets("Sheet1") while automating Excel from another application, without accompanying help on the book / application, is 100% guaranteed to complete the ghost process.

When you do this for all lines. Count, Columns.Count, etc., I think you should also specify a sheet for lists with a list.

2.) When all this is done, the surefire way to find out which line increases the COM object count is to move the oExcel.Quit line right after the Set oExcel = New Excel.Application and continue to execute it again and move that oExcel.Quit to continue and further down until you come across a ghostly process, and that will be your culprit.

I hope this helps fix it, I spent too many hours on a similar problem, I hope this is the same root cause.