7

Why does this line of code cause Excel not to exit?

Excel.Range range = (Excel.Range)ws.Cells[1,1];

If it's because of the casting, then wouldn't this code cause the same problem?

Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet;

I've tried. But this works. Excel will close.

If I use this code. Excel closes.

Excel.Range range = ws.get_Range("A1","A1");

So what's the difference? Yes I know there's like a million of "How to close Excel properly" threads. But since this is a question and not an answer, I decided to ask a new one instead of asking in other people's question.

Here's my code. But of course there's another codes in between. I'm just commenting out everything and slowly trying out which lines causes Excel to not close. I realise that even without using Garbage collector, Excel still closes. I do not want to use a sledgehammer to close Excel.

Thanks.

Excel.Application objExcel = new Excel.Application();
Excel.Workbooks wbs = objExcel.Workbooks;
Excel.Workbook wb = wbs.Open(saveFileDialog1.FileName, Type.Missing,  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet;
//Excel.Range range = (Excel.Range)ws.Cells[1,1];
Excel.Range range = ws.get_Range("A1","A1");

FinalReleaseAnyComObject(range);
FinalReleaseAnyComObject(ws);
wb.Close(Type.Missing, Type.Missing, Type.Missing);
FinalReleaseAnyComObject(wb);
FinalReleaseAnyComObject(wbs);
objExcel.Quit();
FinalReleaseAnyComObject(objExcel);

Currently I've tried until objExcel, wbs, wb and ws. These 4 objects does not cause a problem.

private static void FinalReleaseAnyComObject(object o)
{
    Marshal.FinalReleaseComObject(o);
    o = null;
}

I realise that you cannot reuse the variable as well.

Excel.Range range = ws.get_Range("A1","G1");
range = ws.get_Range("A1", "A1");

This will cause Excel not to close properly too. Instead, use this.

Excel.Range range = ws.get_Range("A1","G1");
FinalReleaseAnyComObject(range);
range = ws.get_Range("A1", "A1");
user607455
  • 479
  • 5
  • 18
  • @user: by close, do you mean crash? It seems that the cast operation should not close Excel deliberately. – p.campbell Feb 11 '11 at 01:42
  • @p.campbell By close, I mean closing Excel properly. Excel or maybe COM objects has a well known problem of not closing properly. i.e Even after I call objExcel.quit(), the Excel process will still remain in task manager. – user607455 Feb 11 '11 at 02:02

1 Answers1

12

There's a hidden Range interface pointer that you can't see, the Cells property returns it. You then apply the indexer expression to it, dereferencing the default Item property of that Range. To get another Range.

This is why it is such a bad, bad idea to try to manage COM interface pointers yourself. Trust the garbage collector to always get it right. GC.Collect() and GC.WaitForPendingFinalizers() if you really, really want to make it quit on demand. Be sure to read this answer to understand why this doesn't always work as expected when you debug your program.

Community
  • 1
  • 1
Hans Passant
  • 922,412
  • 146
  • 1,693
  • 2,536
  • I heard that garbage collector is expensive? Not very sure about it as I haven read into it. – user607455 Feb 11 '11 at 02:04
  • Just tried this "Excel.Range range = (Excel.Range)ws.Cells[1,1];" with Garbage collector and it works! So which is better? Cells or get_Range? Should I avoid GC entirely? – user607455 Feb 11 '11 at 02:10
  • Beautiful explanation Hans. user607455, no, Hans is saying that you must use the GC when cleaning up COM objects. There is no avoiding it. See here for a more detailed discussion: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c/159419#159419 – Mike Rosenblum Feb 13 '11 at 17:19
  • 1
    @user607455, yes, of course you can do so without using the GC, but there is the very real risk that you will be creating objects behind the scenes that you are not aware of. In a medium to large size project, the probability of this climbs to a near certainty. In the long run, experience will show that forcing GC.Collect() and GC.WaitForPendingFinalizers() is best. This is just my opinion, of course, but I have a huge amount of experience with this. (See Also: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c/159419#159419) – Mike Rosenblum Mar 04 '11 at 21:11