Pete's Windows, Office, VB & SQL Blog

I’ve migrated some code from vb2005, via vb 2008 to vb 2010.  I’ve changed the program quite a lot but the Excel class has not been changed.  Now when I run the program one or more excel.exe’s are left in the tasklist.  This used to happen rarely in vb 2005, the vb 2008 version was never run in anger, and it happens all the time in vb 2010.  I’ve read lots of articles on the web saying I must dispose of all the objects, well maybe I should, but …
( the logic works, why should I have to worry, all child objects should be cleared by the xl=nothing at the end)

So it’s poor coding, or perhaps a poor compiler take your choice.

I just need it to work properly – to not leave excel.exe behind.

The key parts or the following code are lifted from http://www.craigmurphy.com/blog/?p=82 comment #11 by Mahdi and converted into vb:

Public Class ExcelAccess

Dim xl As Microsoft.Office.Interop.Excel.Application
Public xlHashtable As New Hashtable()      ‘ Instance has table for existing execl
‘ requires rescouce from COM (add reference): Microsoft Office 11.0 Object Library 2.3, Microsoft Excel 11.0 Object Library
Public Sub New()
CheckExcellProcesses()          ‘ Store existing processes in the Hastable

End Sub

Public Sub ExcelNew()

xl = CType(CreateObject(“excel.Application”), Excel.Application)
xl.Workbooks.Add()
xl.Visible = True

End Sub

Public Sub ExcelQuit()
xl.Application.Quit()
xl = Nothing
KillExcel()
‘Unload(Me)
End Sub

‘ Converted from c

Private Sub CheckExcellProcesses()
Dim AllProcesses As Process()
AllProcesses = Process.GetProcessesByName(“excel”)

Dim iCount As Integer = 0

For Each ExcelProcess As Process In AllProcesses
xlHashtable.Add(ExcelProcess.Id, iCount)
iCount = iCount + 1
Next

End Sub
Public Sub KillExcel()

‘ Remove any excel’s started since this process atarted
Dim AllProcesses As Process()
AllProcesses = Process.GetProcessesByName(“excel”)

‘ check to kill the right process
For Each ExcelProcess As Process In AllProcesses

If (xlHashtable.ContainsKey(ExcelProcess.Id) = False) Then
ExcelProcess.Kill()
End If
Next
AllProcesses = Nothing

End Sub