Which Excel Interop objects to clean up myself and which to clean up by GC.Collect()

Question

Question:

I want to ask a question in response to Mike Rosenblum's answer to this question. The question was about cleaning up Excel interop objects. Several solutions where suggested (e.g. wrappers, not using more than one dot, killing the excel process), but I liked Mike Rosenblum's solution to this problem the most (lengthy article about the topic).

What it basically says is that you don't worry too much about all the references floating around. You just keep some main ones (like ApplicationClass, Workbook and Worksheet). You first invoke garbage collection to clean up all the objects floating around and then explicitly clean up the main references you still have by calling Marshal.FinalReleaseComObject (in reverse order of importance).

Now I have two questions about this.
First: How do I determine to which objects I need to keep a reference? In Mike Rosenblum's example he only keeps Ranges, Worksheets, Workbooks and ApplicationClasses.
Second: If there are more objects, how do I determine the order of cleaning them up (i.e. the "order of importance")?

Thanks in advance.

---

Update 1:

It has been suggested by MattC that for the order, the only thing that is important is that the app is released last. Although in my reference the following sentence:"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." implies that there is more ordering.

nobugz Suggests that setting everything to null and then doing garbage collection will suffice, but that seems to contradict the following quote from Mike Rosenblum's article:"You would think, then, that you can set all your variables = Nothing and then call GC.Collect() at the end, and this does work sometimes. However, Microsoft Office applications are sensitive to the order in which the objects are released and, unfortunately, setting your variables = Nothing and then calling GC.Collect() does not guarantee the release order of the objects."

Update 2:

Some extra info: In my own application, I do a lot of things with a chart. I am setting a lot of properties etc. As I understand, there are many places where I create new COM objects. I tried to make sure I never use double dots, and I tried to call Marshal.FinalReleaseComObject on all objects that I am finished with. I didn't use the wrapper approach because it would introduce a lot of nesting.
EXCEL.exe did not close after my app finished its work. But... it did close when I told my app to do the same work again. Of course a new EXCEL.exe opened which did not close. Now I have removed alllll the Marshal.FinalReleaseComObject calls and the app works exactly the same. The EXCEL.exe stays, until I tell my app to redo the work, but then a new EXCEL.exe starts and stays.

EDIT: Also when I tell my app to do other non-COM related work, after a while the EXCEL.exe disappears, but now no new EXCEL.exe appears.

Not sure what conclusions I can draw from this...

Answer 1

I think as long as application is last you can release them in any order (as long as they aren't null).

Then do a GC.Collect to finally kill the excel.exe process.

Answer 2

I've had a similar problem this is what my catch and finally look like for clean up. I hope it helps.

        .......

        oWB._SaveAs(strCurrentDir +
           strFile, XlFileFormat.xlWorkbookNormal, null, null, false, false,       XlSaveAsAccessMode.xlShared, false, false, null, null);
            sumsheet.Activate();
            oWB.Close(null, null, null);
            oXL.Workbooks.Close();
            oXL.Quit();
        }
        catch (Exception theException)
        {
            theException.ToString();
        }
        #region COM Object Cleanup
        finally
        {
            // Cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oRng);
            //System.Runtime.InteropServices.Marshal.FinalReleaseComObject(sumSheet);
            //System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oSheet);
            //oWB.Close(null, null, null);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oWB);
            oXL.Quit();
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oXL);
        }
        #endregion

EDIT

If you noticed I've commented out the sumSheet + oSheet(Which are my worksheets) because it wasn't needed. This code has solid for me with no problems. I've found by rearranging the order I've gotten errors.

Answer 3

You should have no trouble finding possible live references in your code, they will be fields in your class(es). Or local variables in the cleanup method, that's unlikely. The list provided in the link are just objects that you most likely will store in a field. There could be others, they'll keep Excel just as alive as the Application object.

I don't think I'd recommend the jackhammer approach as advocated in the link, it just hides a potential life reference to an RCW that wraps a dead COM interface. A best you'll have a possibly permanent leak to the RCW object, at worst it crashes your program with an exception when it accidentally references the object. Bugz, to be sure, just not ones you'll easily discover. All you have to do is set your references to null, order doesn't matter, then collect.