Categories
c# com-interop excel interop

How do I properly clean up Excel interop objects?

792

I’m using the Excel interop in C# (ApplicationClass) and have placed the following code in my finally clause:

while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
excelSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();

Although this kind of works, the Excel.exe process is still in the background even after I close Excel. It is only released once my application is manually closed.

What am I doing wrong, or is there an alternative to ensure interop objects are properly disposed of?

6

  • 2

    Are you trying to shut down the Excel.exe without closing your application? Not sure I fully understand your question.

    – Bryant

    Oct 1, 2008 at 17:23

  • 4

    I’m trying to make sure the unmanaged interop objects are disposed of properly. So that there are not Excel processes hanging around even when the user has finished with Excel spreadsheet we created from the app.

    – HAdes

    Oct 1, 2008 at 17:29

  • 4

    If you can try to do it by producing XML Excel files, otherwise please consider VSTO un/managed Memory Management: jake.ginnivan.net/vsto-com-interop

    Apr 25, 2012 at 6:35

  • Does this translate to Excel nicely?

    – Coops

    Nov 26, 2012 at 14:10

  • 2

    See (besides answers below) this support article from Microsoft, where they specifically give solutions to this problem: support.microsoft.com/kb/317109

    – Arjan

    Feb 24, 2015 at 16:43

702

Excel does not quit because your application is still holding references to COM objects.

I guess you’re invoking at least one member of a COM object without assigning it to a variable.

For me it was the excelApp.Worksheets object which I directly used without assigning it to a variable:

Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);

I didn’t know that internally C# created a wrapper for the Worksheets COM object which didn’t get released by my code (because I wasn’t aware of it) and was the cause why Excel was not unloaded.

I found the solution to my problem on this page, which also has a nice rule for the usage of COM objects in C#:

Never use two dots with COM objects.


So with this knowledge the right way of doing the above is:

Worksheets sheets = excelApp.Worksheets; // <-- The important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

POST MORTEM UPDATE:

I want every reader to read this answer by Hans Passant very carefully as it explains the trap I and lots of other developers stumbled into. When I wrote this answer years ago I didn’t know about the effect the debugger has to the garbage collector and drew the wrong conclusions. I keep my answer unaltered for the sake of history but please read this link and don’t go the way of “the two dots”: Understanding garbage collection in .NET and Clean up Excel Interop Objects with IDisposable

22

  • 17

    Then I suggest not using Excel from COM and save yourself all of the trouble. The Excel 2007 formats can be used without ever opening Excel, gorgeous.

    – user7116

    Dec 18, 2009 at 17:35

  • 5

    I did not understand what “two dots” mean. Can you please explain?

    – A9S6

    Jan 27, 2010 at 13:18

  • 22

    This means, you shouldn’t use the pattern comObject.Property.PropertiesProperty (you see the two dots?). Instead assign comObject.Property to a variable and use and dispose that variable. A more formal version of the above rule could be sth. like “Assign com object to variables before you use them. This includes com objects that are properties of another com object.”

    – VVS

    Feb 4, 2010 at 10:37

  • 5

    @Nick: Actually, you don’t need any kind of cleanup, since the garbage collector will do it for you. The only thing you need to do is to assign every COM object to its own variable so the GC knows of it.

    – VVS

    Oct 18, 2010 at 7:42


  • 13

    @VSS thats bollocks, the GC cleans up everything since the wrapper variables are made by the .net framework. It just might take forever for the GC to clean it up. Calling GC.Collect after heavy interop isn’t a bad idear.

    Aug 11, 2011 at 8:28

285

You can actually release your Excel Application object cleanly, but you do have to take care.

The advice to maintain a named reference for absolutely every COM object you access and then explicitly release it via Marshal.FinalReleaseComObject() is correct in theory, but, unfortunately, very difficult to manage 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. In this case, there would be no way to find the cause in the code; you would have to review all your code by eye and hopefully find the cause, a task that could be nearly impossible for a large project.

The good news is that you do not actually have to maintain a named variable reference to every COM object you use. Instead, call GC.Collect() and then GC.WaitForPendingFinalizers() to release all the (usually minor) objects to which you do not hold a reference, and then explicitly release the objects to which you do hold a named variable reference.

You should also release your named references in reverse order of importance: range objects first, then worksheets, workbooks, and then finally your Excel Application object.

For example, assuming that you had a Range object variable named xlRng, a Worksheet variable named xlSheet, a Workbook variable named xlBook and an Excel Application variable named xlApp, then your cleanup code could look something like the following:

// Cleanup
GC.Collect();
GC.WaitForPendingFinalizers();

Marshal.FinalReleaseComObject(xlRng);
Marshal.FinalReleaseComObject(xlSheet);

xlBook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(xlBook);

xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);

In most code examples you’ll see for cleaning up COM objects from .NET, the GC.Collect() and GC.WaitForPendingFinalizers() calls are made TWICE as in:

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

This should not be required, however, unless you are using Visual Studio Tools for Office (VSTO), which uses finalizers that cause an entire graph of objects to be promoted in the finalization queue. Such objects would not be released until the next garbage collection. However, if you are not using VSTO, you should be able to call GC.Collect() and GC.WaitForPendingFinalizers() just once.

I know that explicitly calling GC.Collect() is a no-no (and certainly doing it twice sounds very painful), but there is no way around it, to be honest. Through normal operations you will generate hidden objects to which you hold no reference that you, therefore, cannot release through any other means other than calling GC.Collect().

This is a complex topic, but this really is all there is to it. Once you establish this template for your cleanup procedure you can code normally, without the need for wrappers, etc. 🙂

I have a tutorial on this here:

Automating Office Programs with VB.Net / COM Interop

It’s written for VB.NET, but don’t be put off by that, the principles are exactly the same as when using C#.

6

228

Preface: my answer contains two solutions, so be careful when reading and don’t miss anything.

There are different ways and advice of how to make Excel instance unload, such as:

  • Releasing EVERY com object explicitly
    with Marshal.FinalReleaseComObject()
    (not forgetting about implicitly
    created com-objects). To release
    every created com object, you may use
    the rule of 2 dots mentioned here:
    How do I properly clean up Excel interop objects?

  • Calling GC.Collect() and
    GC.WaitForPendingFinalizers() to make
    CLR release unused com-objects * (Actually, it works, see my second solution for details)

  • Checking if com-server-application
    maybe shows a message box waiting for
    the user to answer (though I am not
    sure it can prevent Excel from
    closing, but I heard about it a few
    times)

  • Sending WM_CLOSE message to the main
    Excel window

  • Executing the function that works
    with Excel in a separate AppDomain.
    Some people believe Excel instance
    will be shut, when AppDomain is
    unloaded.

  • Killing all excel instances which were instantiated after our excel-interoping code started.

BUT! Sometimes all these options just don’t help or can’t be appropriate!

For example, yesterday I found out that in one of my functions (which works with excel) Excel keeps running after the function ends. I tried everything! I thoroughly checked the whole function 10 times and added Marshal.FinalReleaseComObject() for everything! I also had GC.Collect() and GC.WaitForPendingFinalizers(). I checked for hidden message boxes. I tried to send WM_CLOSE message to the main Excel window. I executed my function in a separate AppDomain and unloaded that domain. Nothing helped! The option with closing all excel instances is inappropriate, because if the user starts another Excel instance manually, during execution of my function which works also with Excel, then that instance will also be closed by my function. I bet the user will not be happy! So, honestly, this is a lame option (no offence guys). So I spent a couple of hours before I found a good (in my humble opinion) solution: Kill excel process by hWnd of its main window (it’s the first solution).

Here is the simple code:

[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);

/// <summary> Tries to find and kill process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <returns>True if process was found and killed. False if process was not found by hWnd or if it could not be killed.</returns>
public static bool TryKillProcessByMainWindowHwnd(int hWnd)
{
    uint processID;
    GetWindowThreadProcessId((IntPtr)hWnd, out processID);
    if(processID == 0) return false;
    try
    {
        Process.GetProcessById((int)processID).Kill();
    }
    catch (ArgumentException)
    {
        return false;
    }
    catch (Win32Exception)
    {
        return false;
    }
    catch (NotSupportedException)
    {
        return false;
    }
    catch (InvalidOperationException)
    {
        return false;
    }
    return true;
}

/// <summary> Finds and kills process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <exception cref="ArgumentException">
/// Thrown when process is not found by the hWnd parameter (the process is not running). 
/// The identifier of the process might be expired.
/// </exception>
/// <exception cref="Win32Exception">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="NotSupportedException">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="InvalidOperationException">See Process.Kill() exceptions documentation.</exception>
public static void KillProcessByMainWindowHwnd(int hWnd)
{
    uint processID;
    GetWindowThreadProcessId((IntPtr)hWnd, out processID);
    if (processID == 0)
        throw new ArgumentException("Process has not been found by the given main window handle.", "hWnd");
    Process.GetProcessById((int)processID).Kill();
}

As you can see I provided two methods, according to Try-Parse pattern (I think it is appropriate here): one method doesn’t throw the exception if the Process could not be killed (for example the process doesn’t exist anymore), and another method throws the exception if the Process was not killed. The only weak place in this code is security permissions. Theoretically, the user may not have permissions to kill the process, but in 99.99% of all cases, user has such permissions. I also tested it with a guest account – it works perfectly.

So, your code, working with Excel, can look like this:

int hWnd = xl.Application.Hwnd;
// ...
// here we try to close Excel as usual, with xl.Quit(),
// Marshal.FinalReleaseComObject(xl) and so on
// ...
TryKillProcessByMainWindowHwnd(hWnd);

Voila! Excel is terminated! 🙂

Ok, let’s go back to the second solution, as I promised in the beginning of the post.
The second solution is to call GC.Collect() and GC.WaitForPendingFinalizers(). Yes, they actually work, but you need to be careful here!
Many people say (and I said) that calling GC.Collect() doesn’t help. But the reason it wouldn’t help is if there are still references to COM objects! One of the most popular reasons for GC.Collect() not being helpful is running the project in Debug-mode. In debug-mode objects that are not really referenced anymore will not be garbage collected until the end of the method.
So, if you tried GC.Collect() and GC.WaitForPendingFinalizers() and it didn’t help, try to do the following:

1) Try to run your project in Release mode and check if Excel closed correctly

2) Wrap the method of working with Excel in a separate method.
So, instead of something like this:

void GenerateWorkbook(...)
{
  ApplicationClass xl;
  Workbook xlWB;
  try
  {
    xl = ...
    xlWB = xl.Workbooks.Add(...);
    ...
  }
  finally
  {
    ...
    Marshal.ReleaseComObject(xlWB)
    ...
    GC.Collect();
    GC.WaitForPendingFinalizers();
  }
}

you write:

void GenerateWorkbook(...)
{
  try
  {
    GenerateWorkbookInternal(...);
  }
  finally
  {
    GC.Collect();
    GC.WaitForPendingFinalizers();
  }
}

private void GenerateWorkbookInternal(...)
{
  ApplicationClass xl;
  Workbook xlWB;
  try
  {
    xl = ...
    xlWB = xl.Workbooks.Add(...);
    ...
  }
  finally
  {
    ...
    Marshal.ReleaseComObject(xlWB)
    ...
  }
}

Now, Excel will close =)

16

  • 19

    sad that the thread is already so old that your excellent answer appears that far below, which I think is the only reason for it not being upvoted more times…

    May 4, 2010 at 16:32

  • 15

    I have to admit, when I first read your answer, I thought it was a giant kludge. After about 6 hours of wrestling with this (everything is released, I’ve got no double dots, etc..), I now think your answer is genius.

    – Mark

    Aug 27, 2010 at 15:53

  • 3

    Thanks for this. Been wrestling with an Excel that wouldn’t close no matter what for a couple of days before I found this. Excellent.

    – BBlake

    Oct 19, 2010 at 15:27

  • 2

    @nightcoder: awesome answer, really detailed. Your comments with respect to debug mode are very true and important that you pointed this out. The same code in release mode can often be fine. Process.Kill however, is only good when using automation, not when your program is running within Excel, e.g., as a managed COM add-in.

    Dec 29, 2011 at 18:10

  • 2

    @DanM: Your approach is 100% correct and will never fail. By keeping all your variables local to the method, all references are effectively unreachable by .NET code. This means that when your finally section calls GC.Collect(), the finalizers for all your COM objects will get called with certainty. Each finalizer then calls Marshal.FinalReleaseComObject on the object being finalized. Your approach is therefore simple and fool-proof. Have no fear using this. (Only caveat: if using VSTO, which I doubt that you are, you would need to call GC.Collect() & GC.WaitForPendingFinalizers TWICE.)

    Dec 29, 2011 at 18:14