After running a certain wizard in my application, the user is no longer able to open spreadsheets. Excel locks up and the only way to fix it is to kill the Excel process.
Clearly, I was doing something funky in Excel in the wizard and not cleaning up after myself.
I have a wizard in my application and one of its functions is to copy a spreadsheet to a certain location, extract some data from it, and insert that data into a database. Here's the code for the second step which is the one relevant to my post (and I can explain about those hard-coded values...but I won't):
public static void ExtractCategories( string fieldInputSheet )
Workbook book = m_application.Workbooks.Open( fieldInputSheet, f, t, o, o, o, t, o, o, f, f, o, f, o, o );
Worksheet sheet = (Worksheet) book.Sheets;
Range range = null;
for ( int i = 21; i < 41; i++ )
range = (Range) sheet.Cells[i, 10];
object category = range.Value2;
// Do stuff with the category
book.Close( f, o, o );
(Aside: f, t, and o are module level variables representing false, true, and System.Reflection.Missing.Value respectively. Shortened them because of the sheer number of parameters many Excel functions take.)
OpenExcel and CloseExcel are helper functions to manage all the crap involved with opening and closing Excel from managed code. I'll show you CloseExcel because it has some relevance:
public static void CloseExcel( )
if ( m_application != null )
// Close all workbooks
Workbooks workbooks = m_application.Workbooks;
// Close the application
m_application = null;
Notice the ReleaseComObject calls. Now, hillbillies aren't what you'd call COM-based coders so I won't pretend to know anything about managing COM in .NET. My goto guy for those types of questions is James Kovacs. But I do recall adding the ReleaseComObject calls in many moons ago to combat a similar problem. So what the hey? Let's see if adding some of those to my troubled function will help:
Marshal.ReleaseComObject( sheet );
Marshal.ReleaseComObject( book );
if ( range != null )
Marshal.ReleaseComObject( range );
That covers all the COM-based objects I'm working with in my function. And adding those lines right before the call to CloseExcel solved the problem. I've glossed over articles on this kind of thing and probably read somewhere that managed code isn't able to clean up COM objects.
Note that I checked Task Manager while all this was running and even though the COM objects are released, the EXCEL process is still running after this function finishes. Not sure how to get rid of it but I suspect it sticks around just to be helpful. The same way WINWORD does when you use Word as your e-mail editor in Outlook. In any case, I don't think it's hurting.