Monday, March 29, 2004

VS2003 & Excel "Leaking..." and Forcing Garbage Collection

Using VS2003, I needed to automate Excel 9 (Excel 2000).
Everything seemed to be working ok, EXCEPT that the Excel instance was not cleaning up...

I have the Excel creation and destruction in a function (call it InfoGather) that gets called for each of ### XLS's.

If I called InfoGather 100 times, I'd end up with 100 instances of Excel. Yeah, that's ugly.

I made sure I was ".Quit"'ing the excel application object, setting everything to nothing, etc. But still had the problem.

So to isolate the issue, I created a stub that JUST created an Excel instance and destroyed it.

And guess what? It had the same problem! Cool... now I know it wasn't my code.

So I did the google thing, and didn't find anything. Reading an article on an other subject, I saw the author was calling the garbage collector.

"I shouldn't have to call the GC... right? I mean .Net 1.1 is supposed to do that for me?..."

Well I decided to give it a try anyway and... IT WORKED! No more Excel instance "leaks"! Rock on...

To log this for the future, I'm blogging... :)

Here are two simple examples. The first "leaks" instances and the second works as you would expect (at the end of the function, at the destroy, the Excel instance goes away...).

Leaking Example:
'------- Begin Sample ---------
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

For lloop As Integer = 0 To 10
StartandStopExcel()
Next lloop

MsgBox("Done")
End Sub

Private Sub StartandStopExcel()
Dim mseExcel As Excel.Application

mseExcel = New Excel.Application


mseExcel.Quit()
mseExcel = Nothing

End Sub

'------- End Sample ---------


Good/Working Example:
'------- Start Sample ---------
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

For lloop As Integer = 0 To 10
StartandStopExcel()
Next lloop

MsgBox("Done")
End Sub

Private Sub StartandStopExcel()
Dim mseExcel As Excel.Application

mseExcel = New Excel.Application


mseExcel.Quit()
mseExcel = Nothing

GC.Collect()

End Sub
'------- Start Sample ---------


The only difference between these two samples is that the second forces a garbage collection.

Interesting...

No comments: