Using Excel Interop Objects

In the past I have worked with the new Open XML standards to create Word Documents. My most recent forray into the world of Microsoft Office was to manipulate an Excel spreadsheet or two to create new sheets, insert rows and columns, and even create a pivot table programmatically.



The first thing that I found was that when my program closed, Excel.exe was still running on my machine. I found this because the next time I tried running the program it popped up a message saying that the spreadhseet was locked for editing and that I could only open a read only copy. So I opened up Task Manager and found multiple instances of Excel.exe running.



So began the searching of the Internet tubes for information about why those processes remained. I found a number of articles that described cleaning up COM objects and that if you miss one of them then the Excel.exe process stays open.



After spending a good day listing all of the COM objects out and making sure I cleaned them all up, I had a program that did not leave behind any Excel processes.



How did I clean up those objects? Good question.



The first thing I did was to make a list of all of the comp objects that I created and here is the first trick:



Anywhere where you create COM objects in your code should not look like this:



Dim WorkBook = Application.WorkBooks.Open(path to file)



You need to create separate objects for the collections and the individual objects and then clean up all of those objects. The appropriate way to get a workbook object is as follows:



Dim oWorkBooks = app.WorkBooks

Dim WorkBook = oWorkBooks.Open(path to file)



Now you can clean up both of those COM objects.



Back to the actual cleanup of the objects:



I created a function to perform a couple of these steps since they are required of every COM object.



Release the COM object for that variable - System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBook)



Call FinalRelease to make sure the object is freed up - System.Runtime.InteropServices.Marshal.FinalReleaseComObject(WorkBook)



Set the variable equal to Nothing or Null - WorkBook = Nothing



This will clear the variables, but the objects could still be in memory until Garbage Collection happens. You can speed this process along by calling:



GC.Collect()

GC.WaitForPendingFinalizers()



The second one waits for any stragglers and then cleans then up.



This should get rid of any Excel.exe processes that were hanging around.



Note that if your program crashes and you have not handled that by cleaning up these objects, you will still have processes hanging out there.