How do you kill excel when you are finished?

mskeel · December 20, 2005

I'm following these pages from Mircrosoft:

http://support.microsoft.com/?kbid=317109

http://support.microsoft.com/kb/306022/

I have created the following methods that are called when you click on a button.

'Almost a verbatim copy of the sample code in one of the links above
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
       Dim oExcel As Object
       Dim oBook As Object
       Dim oSheet As Object

       'Start a new workbook in Excel.
       oExcel = CreateObject("Excel.Application")
       oBook = oExcel.Workbooks.Add

       'Add data to cells of the first worksheet in the new workbook.
       oSheet = oBook.Worksheets(1)
       oSheet.Range("A1").Value = "Last Name"
       oSheet.Range("B1").Value = "First Name"
       oSheet.Range("A1:B1").Font.Bold = True
       oSheet.Range("A2").Value = "Doe"
       oSheet.Range("B2").Value = "John"
       oSheet.Range("A3").Value = "BARF"
       oSheet.Range("B3").Value = "POISON"

       'Save the Workbook and quit Excel.
       oBook.SaveAs("C:\Documents and Settings\test\Desktop\" & "Book1.xls")
       oSheet = Nothing
       oBook = Nothing
       oExcel.Quit()
       oExcel = Nothing
       GC.Collect()
   End Sub

This is my code that I've created to reproduce the results and more closely match the automated excel output of my data:

   Private Sub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
       Dim oExcel As Object
       Dim oBook As Object
       Dim oSheet As Object

       'Start a new workbook in Excel.
       oExcel = CreateObject("Excel.Application")
       oBook = oExcel.Workbooks.Add

       ''Set the headers, colunm-wise
       oSheet = oBook.Worksheets(1)
       oSheet.Range("B1").Value = "Some"
       oSheet.Range("C1").Value = "Header"
       oSheet.Range("D1").Value = "For me"
       oSheet.Range("E1").Value = "TOOOOOOOoooo"
       oSheet.Range("F1").Value = "Experiment"
       oSheet.Range("G1").Value = "WITH!"

       oSheet.Range("B1", "G1").ColumnWidth = 20
       oSheet.Range("B1", "G1").Font.Bold = True
       ''currentWorksheet.Range("B2", "G2").HorizontalAlignment = xlCenter
       oSheet.Range("A1").ColumnWidth = 50

       For i As Integer = 0 To 5
           oSheet.Range("A" + (i + 2).ToString()).Value = "Super" + i.ToString + " - " + "Duper" + (i * 5).ToString
           oSheet.Range("B" + (i + 2).ToString()).Value = i.ToString + i.ToString
           oSheet.Range("C" + (i + 2).ToString()).Value = "MEANNNN" + i.ToString
           oSheet.Range("D" + (i + 2).ToString()).Value = "MEGAAAAA!!" + i.ToString
           oSheet.Range("E" + (i + 2).ToString()).Value = "ULTRA MEGA" + i.ToString
           oSheet.Range("F" + (i + 2).ToString()).Value = "WICKED AWESOME" + i.ToString
           oSheet.Range("G" + (i + 2).ToString()).Value = "GREATOO" + i.ToString
       Next


       'Save the Workbook and quit Excel.
       oBook.SaveAs("C:\Documents and Settings\test\Desktop\" & "Super.xls")
       oSheet = Nothing
       oBook = Nothing
       oExcel.Quit()
       oExcel = Nothing
       GC.Collect()
   End Sub

As you can see, the only thing that I changed was what was written in the "body" of the method -- I followed the same basic formula for setting up my Excel objects, doing something, and then shutting everything down.

Here's the part that doesn't make any sense. If you run these methods, the first one will execute and then kill the Excel process like it is supposed to do. The second method will not. Both methods succesfully write what they are supposed to, but the second one does not properly kill Excel when it is finished. What's going on here?

Now here's something that is even more annoying. Make a third method in a new button handler method that looks like this:

 Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
       Dim oExcel As Object
       Dim oBook As Object
       Dim oSheet As Object

       'Start a new workbook in Excel.
       oExcel = CreateObject("Excel.Application")
       oBook = oExcel.Workbooks.Add

       ''Set the headers, colunm-wise
       oSheet = oBook.Worksheets(1)
       oSheet.Range("B1").Value = "Some"
       oSheet.Range("C1").Value = "Header"
       oSheet.Range("D1").Value = "For me"
       oSheet.Range("E1").Value = "TOOOOOOOoooo"
       oSheet.Range("F1").Value = "Experiment"
       oSheet.Range("G1").Value = "WITH!"

       oSheet.Range("B1", "G1").ColumnWidth = 20
       oSheet.Range("B1", "G1").Font.Bold = True
       ''currentWorksheet.Range("B2", "G2").HorizontalAlignment = xlCenter
       oSheet.Range("A1").ColumnWidth = 50

       For i As Integer = 0 To 5
           oSheet.Range("A2").Value = "AYEEE!"
           oSheet.Range("B2").Value = "Bogus"   '' + i.ToString  ''Uncomment me for something that doesn't make sense!
           oSheet.Range("C2").Value = "MEANNNN" + i.ToString
           oSheet.Range("D2").Value = "MEGAAAAA!!" + i.ToString
           oSheet.Range("E2").Value = "ULTRA MEGA" + i.ToString
           oSheet.Range("F2").Value = "WICKED AWESOME" + i.ToString
           oSheet.Range("G2").Value = "GREATOO" + i.ToString
       Next


       'Save the Workbook and quit Excel.
       oBook.SaveAs(Me._fileName)
       oSheet = Nothing
       oBook = Nothing
       oExcel.Quit()
       oExcel = Nothing
       GC.Collect()
   End Sub

Again, the same basic formula but this one works as it is supposed to. The major differences -- the Range is not calculated on the fly with an i.ToString() and the number of i.ToString()'s assigned to Values are reduced. Recompile and rerun the program, this time uncommenting the ToString I have commented out above ("Uncomment me for something that doesn't make sense!"). Surprisingly, Excel doesn't clean up any more.

What is going on here? Am I missing some great fundamental truth of talking to Excel via COM? Why does this work sometimes but not others?

herilane · December 21, 2005

In my experience, the great fundamental truth is simply that automating Office from .NET is unpredictable.

One thing that is missing from your code (and one of MS's examples - though it is present in another) is closing the workbook. Quitting Excel should normally take care of that automatically, but not always.

oBook.Close()

The other thing you can try is break down all calls to the Excel object model and do them one step at a time, as outlined in the first of the KB articles you link to. I don't know what the logic of that rule is - sounds about as logical as wand-waving and incantations to me. But it sounds to me like some part of the COM Interop process just can't do two things at a time and needs things to be simple. That would explain (in a very broad sense of the word) why uncommenting that ToString makes things break down.

mskeel · December 21, 2005

I too was skeptical of not explicitling holding a pointer to the workbooks object, but it worked just fine with out it and then didn't work in the second method so I didn't think that would be the case. I gave it a shot and got the same results (except for the third case which stopped cleaning up the process unless you commented out one more ToString() method). Here's my new skeleton for the three methods with different bodies above:

        Dim oExcel As Object
       Dim oBook As Object
       Dim oBooks As Object
       Dim oSheet As Object

       'Start a new workbook in Excel.
       oExcel = CreateObject("Excel.Application")
       oBooks = oExcel.Workbooks
       oBook = oBooks.Add

       ''Set the headers, colunm-wise
       oSheet = oBook.Worksheets(1)
       ''
       ''Do something here that seems to make the process not want to die sometimes
       ''

       'Save the Workbook and quit Excel.
       oBook.SaveAs(fileName)
       oSheet = Nothing
       oBook.Close()
       oBook = Nothing
       oBooks = Nothing
       oExcel.Quit()
       oExcel = Nothing
       GC.Collect()

I did try the marshling trick (NAR tutorial) and that worked just fine...until I tried to write something out to the file. Then that choked too.

AND here's a funny work around. I have found that if I assign the string to a variable before assigning it to a Range.Value, then everything works fine in the strange, third case. This means that in the strange, third case, if I do the following:

Dim tempString = "MEANNNN" + i.ToString
oSheet.Range("C2").Value = tempString

then it will write everything to file properly, and exit correctly no matter how many time I toString stuff. Doing the same thing in the second case where I programaticly iterate through rows still fails to clean up the process.

Edited December 21, 2005 by mskeel

bri189a · December 22, 2005

I conquered that darn beast a while ago...the secret lies in how you create the references and destroy them (that's some old code of mine on this post, so please over-look the glaring bad coding, the 'what's between the lines' is the important part) - the thing is to never talk to strangers:

object cell = myWorkbook.Sheets[1].Cells[2] or whatever the exact syntax is will cause issues. Whereas if you have

object tempSheets = myWorkbook.Sheets;

object tempSheet = Sheets.Item(1);

object tempCells = tempSheet.Cells;

object cell = tempCells.Item(5);

and clean up the resources with 'ReleaseComObject' you won't have any resources, but now that I'm thinking about it I think all you really have to do is set the items to null and just do the ReleaseComObject on the ExcelApp itself...think I discovered that long after I wrote that (beginner) class. I have some current day code that does all of this somewhere at work, if I have time I'll see if I can't figure out where I buried it... I really need one of those library tracking tools!

http://www.xtremedotnettalk.com/showpost.php?p=407231&postcount=7

Hope it helps!

Edited December 22, 2005 by bri189a

Mike_R · December 22, 2005

Using Marshal.ReleaseComObject() properly is almost impossible to do corerctly, unfortunately. It's extremely easy to make a mistake -- if you forget to release even one object absolutely anywhere in your code, then Excel will hang.

Mskeel, your code by contrast looks pretty good. I don't see any flaw in it and the only thing I can suggest is possibly adding a call to GC.WaitForPendingFinalizers() after calling GC.Collect(). But other than that, it looks clean, best I can tell.

If all else fails, you can close the Excel instance by force via the Process class and then calling Process.Kill(). An example of this can be found here: http://www.xtremevbtalk.com/showthread.php?p=956122#post956122

Hope this helps!

Mike

mskeel · December 22, 2005

If all else fails, you can close the Excel instance by force via the Process class and then calling Process.Kill()

Yeah...I was hoping to be able to do it "the right way" but the COM stuff seems just so finicky. It's just so strange that how, in the third example of the first post, the process will end properly with that line commented out and then not end when the statement is in. I'm going to try the kill process but my curiousity is really peaked. What is so special about that statement?

Everyone, thanks for all your help on this so far. I greatly appreciate it.

mskeel · December 22, 2005

I followed the walkthough and it worked like a charm. I just wish I understood why everything else was acting so funny and why it had to come to that.

Thanks again for all your help. I know this issue comes up a lot, but I appreciate the time you gave me.

bri189a · December 22, 2005

Using Marshal.ReleaseComObject() properly is almost impossible to do corerctly, unfortunately. It's extremely easy to make a mistake -- if you forget to release even one object absolutely anywhere in your code, then Excel will hang.

Mskeel, your code by contrast looks pretty good. I don't see any flaw in it and the only thing I can suggest is possibly adding a call to GC.WaitForPendingFinalizers() after calling GC.Collect(). But other than that, it looks clean, best I can tell.

If all else fails, you can close the Excel instance by force via the Process class and then calling Process.Kill(). An example of this can be found here: http://www.xtremevbtalk.com/showthread.php?p=956122#post956122

Hope this helps!
Mike

Well I've never had Excel hang in the process' after I started coding down that road with Office applications, so that old version may not be very clean, but it did the job consistently and I never had a problem with Marshal.ReleaseComObject().

Mike_R · December 28, 2005

Mskeel,

Glad it worked for you. :) Once you get the hang of it, as Bri189a says, it's really not that hard...

Bri189a,

Well, there are a few issues here that can make it easy or hard. Once you get used to it, it's pretty easy, but the early days can be very frustrating. Also, if you are controlling Excel from an in-process DLL add-in, you cannot make Excel hang no matter how badly you code.

If not using a DLL and are controlling from an out-of-process EXE, then using the Marshal.ReleaseComObject() approach requires some rather awkward coding. Here's an older tutorial I wrote using this technique:

http://www.xtremevbtalk.com/showthread.php?t=129690

But I find it extremely awkward to use in practice and I really do not recommend it to anyone...

-- Mike

John_0025 · January 6, 2006

Using Marshal.ReleaseComObject() properly is almost impossible to do corerctly, unfortunately. It's extremely easy to make a mistake -- if you forget to release even one object absolutely anywhere in your code, then Excel will hang.

Mskeel, your code by contrast looks pretty good. I don't see any flaw in it and the only thing I can suggest is possibly adding a call to GC.WaitForPendingFinalizers() after calling GC.Collect(). But other than that, it looks clean, best I can tell.

If all else fails, you can close the Excel instance by force via the Process class and then calling Process.Kill(). An example of this can be found here: http://www.xtremevbtalk.com/showthread.php?p=956122#post956122

Hope this helps!
Mike

:eek: I don't think killing the process is an option. Firstly I think you can only do this if you run the program with local admin rights. I'm sure other users won't get permission to do this by default. Secondly what if the user was already using Excel? You'd find yourself in a bit of trouble ;)

I found GC.WaitForPendingFinalizers() helped me.

mskeel · January 6, 2006

Secondly what if the user was already using Excel?

I too was worried about that so I tested it out. Luckily, this technique only kills the process that you programatically started. All other Excel processes stay open.

Also, it appears that a limited user account can kill any process they own, including an Excel process spawned programaticly.

Edited January 6, 2006 by mskeel

Sign In

How do you kill excel when you are finished?

Recommended Posts

mskeel

herilane

mskeel

bri189a

Mike_R

mskeel

mskeel

bri189a

Mike_R

John_0025

mskeel

Browse

Activity