2

Through excel VBA I'm opening a workbook and closing it, thus quitting the Excel application. But it is still running in the Task manager, which prevents my addin 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
Horaciux
  • 6,322
  • 2
  • 22
  • 41
user3911840
  • 151
  • 2
  • 6
  • Did you look at this: http://stackoverflow.com/questions/21291083/vba-automation-of-excel-leaves-a-process-in-memory-after-quit – Al Lelopath Aug 05 '14 at 20:01

1 Answers1

2

When I run into this issue with ghost Excel processes after creating and manipulating an excel application/workbook (usually from Access) the solution I found is always the same - but after doing some testing on my end, the code you have there does not create a ghost process for me. Either way implementing this fix should work, so here are two suggestions:

1.) You need to fully qualify every reference when you are creating/manipulating a new excel application - COM keeps track of the number of open objects by a simple count - when you open a new excel process it ticks up by 1, but if you make an unqualified reference 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 ticks up again, and now even when they're all closed the internal count of COM objects is still at 1 and Excel stays open. (Obviously you don't have to type out the full thing each time, just when creating the objects.) So I would use - 

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

Even if it isn't what's causing the problem now, doing something like Sheets("Sheet1") when automating Excel from another application, without the accompanying workbook/application reference is 100% guarenteed to make a ghost process.

When you do that for all the Rows.Count, Columns.Count, etc, I think you should specify the sheet for the comboboxes too.

2.) When all thats done, the surefire way to figure out which line is incrementing that COM object count is to move the oExcel.Quit line to just after the Set oExcel = New Excel.Application line , and keep re-running it and moving that oExcel.Quit line further and further down until you encounter the ghost process, and that'll be your culprit.

I hope that helps fixed it, I wasted way too many hours a similar problem, hopefully it's the same root cause.

Acantud
  • 488
  • 3
  • 12