How can I get Excel to close when I'm done with it?

Question

This is in a COM API Word AddIn. And yes normally Hans Passant's advice to let .NET clean everything up works.

But it is not working for the following case - and I have tested running normally (no debugger) and have narrowed it down to this specific code:

private Chart chart;
private bool displayAlerts;
private Application xlApp;


Chart chart = myShape.Chart;

ChartData chartData = chart.ChartData;
chartData.Activate();

WorkbookData = (Workbook)chartData.Workbook;
xlApp = WorkbookData.Application;
displayAlerts = xlApp.DisplayAlerts;
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
WorksheetData = (Worksheet)WorkbookData.Worksheets[1];
WorksheetDataName = WorksheetData.Name;
WorksheetData.UsedRange.Clear();

// ... do a bunch of stuff including writing to the worksheet

xlApp.DisplayAlerts = displayAlerts;
WorkbookData.Close(true);

I think the problem is likely Word is giving me this workbook and so who knows what it is doing to instantiate Excel. But even after I exit Word, the Excel instance is still running.

Again, in Word (not Excel), accessing a chart object to update the data in the chart.

Could you also show the clean-up, please? Have you tried xlApp.Quit? Does Excel "quit" when the entire code goes out-of-scope? — Cindy Meister, Jan 01 '19 at 17:52
@CindyMeister Hi cindy! The above code is everything, the WorkbookData.Close(true); is my clean-up. Is it safe to call xlApp.Quit() in all cases? And yes, even after I exit Word, the Excel zombie instance is still running. — David Thielen, Jan 03 '19 at 00:08
I did some quick testing in the VBA environment since that's simpler to monitor and can duplicate what you observe *unless* I correctly release everything. Referencing your code that would mean: `WorksheetData = null; WorkbookData = null; xlApp.Quit(); xlApp = null;` You might also need to use `Excel.Range usedRng = WorksheetData.UsedRange ; usedRng.Clear(); usedRng = null;` to ensure cleaning up as C# does have a tendency to create objects behind the scenes when COM dot-notation is used that don't get cleaned up. Then the standard (doubled) GC.Collect(); GC.WaitForPendingFinalizers(); stuff — Cindy Meister, Jan 03 '19 at 12:35
Note: by default `xlApp` is not needed or used when manipulating charts. I can see you're instantiating it in order to hide the Excel window. But the object model isn't designed to handle cleaning that up - it assumes it isn't present. So you need to handle it. Or you could try putting everything in a `using` block, see if that takes care of it. Or a combination (xlApp.Quit inside the block and let the block take care of setting to null). — Cindy Meister, Jan 03 '19 at 12:37
@CindyMeister - that did it (required every one of those steps). If you write that as an answer I'm happy to mark it the answer. thanks - dave — David Thielen, Jan 06 '19 at 19:18

score 1 · Accepted Answer · answered Jan 06 '19 at 19:34

COM objects need to be released completely, else "orphaned" objects can keep an application in memory, even after the code that called it goes out-of-scope.

This particular case may be special (compared to other code you've used previously) due to using xlApp. By default, an Excel Application object is not needed or used when manipulating charts with the object model introduced in Office 2007 (I think it was). It's used in the code in the question in order to hide the Excel window, which is visible by default (and by design). But the object model isn't designed to handle cleaning that up - it assumes it isn't present...

In my tests, the object is released correctly when (referencing the code in the question):

All Excel objects are set to null in the reverse order they are instantiated, being sure to quit the Excel application before trying to set that object to null:

WorksheetData = null; 
WorkbookData = null; 
xlApp.Quit(); 
xlApp = null;

Then, C# has a tendency to create objects behind the scenes when COM dot-notation is used - these don't always get cleaned up (released) properly. So it's good practice to create an object for each level of the hierarchy being used. (Note: VBA doesn't have this problem, so any code picked up from a VBA example or the macro recorder needs to be re-worked in this respect.) From the code in the question this affects WorksheetData.UsedRange.Clear();

Excel.Range usedRng = WorksheetData.UsedRange; 
usedRng.Clear(); 
usedRng = null;

And the standard clean up, to make sure everything is released at a predictable moment:

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

When things like this crop up, I always refer to ".NET Development for Office" by Andrew Whitechapel. That's really "bare bones" and some of it no longer relevant, given the changes to C# over the years (to make it "easier to use" in the way VB.NET is "easier"). But how COM interacts with .NET hasn't changed, deep down...

How can I get Excel to close when I'm done with it?

1 Answers1