Excel 2007 Hangs When Closing via .NET

Question

I have a Visual Basic .NET program which needs to open and close an Excel spreadsheet. Opening and reading the spreadsheet work fine, but trying to close the Excel 2007 application causes it to hang. It seems to close, but if you look in the task manager the application is still running. The code that I'm using to close it is

wbkData.Close(saveChanges:=False)
appExcel.Quit()
wbkData = Nothing
appExcel = Nothing

How can I get Excel to close properly?

Answer 1

I found a solution in the MSDN Excel Blog which worked for me. It's explained as

There are two problems with the above:

(1) Although the code appears to dispose of the 'wbkData' object first, etc., the code above does not actually enforce this as the .NET Garbage Collection procedure can dispose of its objects in any order. (GC is non-deterministic in order, not just non-deterministic in timing.)

(2) Commands such as 'wsh = wbkData.Workssheets.Item(1)' -- or lines like it -- are very common and will create an RCW object wrapping a 'Worksheets' object. You won't have a variable holding a reference to it, so you don't generally think about it, but this RCW object will not be disposed until the next Garbage Collection. However, the code above calls GC.Collect() last, and so the RCW is still holding a reference to this 'Worksheets' object when appExcel.Quit() is called. Excel hangs as a result.

The final code looks like

GC.Collect()
GC.WaitForPendingFinalizers()

wbkData.Close(SaveChanges:=False)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wbkData) : wbkData = Nothing
appExcel.Quit()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(appExcel) : appExcel = Nothing

Answer 2

The answer to your question has been covered here i think: How to properly clean up excel interop objects in c

i cant see from your code sample, but basically, always assign your excel objects to local variables, never going 'two dots down', like this:

//FAIL

Workbook wkBook = xlApp.Workbooks.Open(@"C:\mybook.xls");

instead ref each obj individually:

//WIN

Worksheets sheets = xlApp.Worksheets;
Worksheet sheet = sheets.Open(@"C:\mybook.xls");
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

.NET creates a wrapper for the COM object that is invisible to you and is not released until the GC weaves its magic.

Until I discovered this, I was running the hacky code below in an ASP.NET application each time i created a new workbook that that checks the age of the excel.exe process and kills any that are over a minute old:

//force kill any excel processes over one minute old.
try
{
    Process[] procs = Process.GetProcessesByName("EXCEL");
    foreach (Process p in procs)
    {
     if (p.StartTime.AddMinutes(1) < DateTime.Now)
     {
      p.Kill(); 
     }  
    }  
}
catch (Exception)
{}

Answer 3

Hey Eric,

I wrote that post you mentioned on the Excel Team Blog...

I also discussed this issue previously on StackOverflow for the question How to properly clean up Excel interop objects in C#.

The first answer for that question was marked as "correct" and got 11 votes, but I assure you that that policy is extremely difficult to utilize properly in practice. If one ever slips anywhere and uses "two dots", or iterates cells via a for each loop, or any other similar kind of command, then you'll have unreferenced COM objects and risk a hang -- and there will be no way to find the cause of that in the code.

Instead, the cleanup procedure you are adopting is definitely the way to go.

-- Mike