How do you kill excel when you are finished?

[mskeel]

Im 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 Ive 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.

Heres the part that doesnt 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. Whats going on here?

Now heres 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 doesnt 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 doesnt make sense!"). Surprisingly, Excel doesnt 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]

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 MSs 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 dont 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 cant 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]

I too was skeptical of not explicitling holding a pointer to the workbooks object, but it worked just fine with out it and then didnt work in the second method so I didnt 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). Heres 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 heres 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.

 

[bri189a]

I conquered that darn beast a while ago...the secret lies in how you create the references and destroy them (thats some old code of mine on this post, so please over-look the glaring bad coding, the whats 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 wont have any resources, but now that Im 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 Ill see if I cant figure out where I buried it... I really need one of those library tracking tools!

http://www.computerhelp.forum/showpost.php?p=407231&postcount=7

Hope it helps!

 

[Mike_R]

Using Marshal.ReleaseComObject() properly is almost impossible to do corerctly, unfortunately. Its 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 dont 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.computerhelp.forum/showthread.php?p=956122#post956122

Hope this helps!
Mike

 

[mskeel]

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. Its 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. Im 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]

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]

Using Marshal.ReleaseComObject() properly is almost impossible to do corerctly, unfortunately. Its 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 dont 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.computerhelp.forum/showthread.php?p=956122#post956122

Hope this helps!
Mike

Well Ive 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]

Mskeel,

Glad it worked for you. Once you get the hang of it, as Bri189a says, its 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, its 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. Heres an older tutorial I wrote using this technique:

http://www.computerhelp.forum/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]

Using Marshal.ReleaseComObject() properly is almost impossible to do corerctly, unfortunately. Its 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 dont 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.computerhelp.forum/showthread.php?p=956122#post956122

Hope this helps!
Mike

I dont think killing the process is an option. Firstly I think you can only do this if you run the program with local admin rights. Im sure other users wont get permission to do this by default. Secondly what if the user was already using Excel? Youd find yourself in a bit of trouble

I found GC.WaitForPendingFinalizers() helped me.

 

[mskeel]

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.