StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can actually release your Excel Application object cleanly, but you do have to take care. </p> <p>The advice to maintain a named reference for absolutely every COM object you access and then explicitly release it via <code>Marshal.FinalReleaseComObject()</code> 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 <code>for each</code> 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.</p> <p>The good news is that you do not actually have to maintain a named variable reference to every COM object you use. Instead, call <code>GC.Collect()</code> and then <code>GC.WaitForPendingFinalizers()</code> 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. </p> <p>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.</p> <p>For example, assuming that you had a Range object variable named <code>xlRng</code>, a Worksheet variable named <code>xlSheet</code>, a Workbook variable named <code>xlBook</code> and an Excel Application variable named <code>xlApp</code>, then your cleanup code could look something like the following:</p> <pre><code>// 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); </code></pre> <p>In most code examples you'll see for cleaning up COM objects from .NET, the <code>GC.Collect()</code> and <code>GC.WaitForPendingFinalizers()</code> calls are made TWICE as in:</p> <pre><code>GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); </code></pre> <p>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 <em>next</em> garbage collection. However, if you are not using VSTO, you should be able to call <code>GC.Collect()</code> and <code>GC.WaitForPendingFinalizers()</code> just once.</p> <p>I know that explicitly calling <code>GC.Collect()</code> 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 <code>GC.Collect()</code>.</p> <p>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. :-)</p> <p>I have a tutorial on this here:</p> <p><a href="http://www.xtremevbtalk.com/showthread.php?t=160433" rel="noreferrer">Automating Office Programs with VB.Net / COM Interop</a></p> <p>It's written for VB.NET, but don't be put off by that, the principles are exactly the same as when using C#.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload