Releasing COM objects in .NET from within enumerations and other reference scenarios

Enumerating

Your sheet loop variable is, indeed, not being released. When writing interop code for excel you have to constantly watch your RCWs. In preference to using foreach enumertions I tend to use for as it makes me realise whenever I've grabbed a reference by having to explicitly declare the variable. If you must enumerate, then at the end of the loop (before you leave the loop) do this:

if (Marshal.IsComObject(sheet)) {
    Marshal.ReleaseComObject(sheet);
}

And, be careful of continue and break statements that leave the loop before you have released your reference.

Intermediates

It depends on whether or not the intermediate is actually a COM object (book.Sheets is) but if it is then you need to first get a reference to it in a field, then enumerate that reference, and then ensure you dispose of the field. Otherwise you are essentially "double dotting" (see below):

using xl = Microsoft.Office.Interop.Excel;
...
public void DoStuff () {
    ...
    xl.Sheets sheets = book.Sheets;
    bool sheetsReleased = false;
    try {
        ...
        foreach (xl.Sheet in sheets) { ... try, catch and dispose of sheet ... }
        ... release sheets using Marshal.ReleaseComObject ...
        sheetsDisposed  = true;
    }
    catch (blah) { ... if !sheetsDisposed , dispose of sheets ... }
}

The above code is the general pattern (it gets lengthy if you type it in full so I have only focussed on the important parts)

What about errors?

Be fastidious in your use of try ... catch ... finally. Make sure that you use this very carefully. finally does not always get called in the case of things like stack overflow, out of memory, security exceptions, so if you want to ensure you clean up, and don't leave phantom excel instances open if your code crashes, then you must conditionally execute reference releasing in the catch before exceptions are thrown.

Therefore, inside every foreach or for loop, you also need to use try ... catch ... finally to make sure that the enumeration variable is released.

Double dotting

Also do not "double dot" (only use a single period in lines of code). Doing this in foreach is a common mistake that is easy for us to do. I still catch myself doing it if I've been off doing non-COM C# for a while as it is more and more common to chain periods together due to LINQ style expressions.

Examples of double dotting:

• item.property.propertyIWant
• item.Subcollection[0] (you are calling SubCollection before then calling an indexer property on that subcollection)
• foreach x in y.SubCollection (essentially you are calling SubCollection.GetEnumerator, so you are "double dotting" again)

Phantom Excel

The big test, of course, is to see if Excel remains open in the task manager once your program exits. If it does then you probably left a COM reference open.

References

You say you have researched this heavily, but in case it helps, then a few of the references that I found helpful were:

• The mapping between interface pointers and runtime callable wrappers (RCWs)
• VSTO and COM Interop
• ReleaseComObject (cbrumme)

Robust solutions

One of the above references mentions a helper he uses for foreach loops. Personally, if I'm doing more than a simple "script" project then I'll first spend time on developing a library specifically wrapping the COM objects for my scenario. I have a common set of classes now that I reuse, and I've found that the time invested in setting that up before doing anything else is more than recovered in not having to hunt down unclosed references later on. Automated testing is also essential to help with this and reaps rewards for any COM interop, not just Excel.

Each COM object, such as Sheet, will be wrapped in a class that implements IDisposable. It will expose properties such as Sheets which in turn has an indexer. Ownership is tracked all the way through, and at the end if you simply dispose of the master object, such as the WorkbookWrapper, then everything else gets disposed of internally. Adding a sheet, for instance, is tracked so a new sheet will also be disposed of.

While this is not a bulletproof approach you can at least rely on it for 95 % of the use cases, and the other 5 % you are totally aware of and take care of in the code. Most importantly, it is tested and reusable once you have done it the first time.