Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  .NET Framework  »  50 Ways to Kill Excel  »  Excel Closure
 »  Home  »  Windows Development  »  Interop  »  50 Ways to Kill Excel  »  Excel Closure
50 Ways to Kill Excel
by Scott Rutherford | Published  06/14/2006 | .NET Framework Interop | Rating:
Excel Closure

1. Ask Nicely

While there are concepts in this article you can apply generally, this section will deal directly with the Microsoft Excel application. As our first steps in closing this app, we’ll make use of the methods exposed by this program. The following code bit first tells Excel not to prompt for Save, etc. upon closure. Unless you want your users interacting directly with Excel, then you should set DisplayAlerts to False. In an environment such as ASP.NET, where no desktop session is available, this must be set to False. Then call the Quit() method exposed by the Excel application. Surprisingly, you’ll find that calling Quit has no effect nearly 100% of the time--hence the need for this article.
        App.DisplayAlerts = False
        App.Quit() ' ask nicely :)

Of course you need to make sure that all screens in the Excel app are closed out before the application itself is closed. The following code snippet closes all workbooks and their worksheets first, before exiting the application.

        If Not App.Workbooks Is Nothing Then
          Dim wb As Microsoft.Office.Interop.Excel.Workbook
          Dim ws As Microsoft.Office.Interop.Excel.Worksheet
          For Each wb In App.Workbooks
            For Each ws In wb.Worksheets
              ws = Nothing
            Next
            wb.Close(False)
            wb = Nothing
          Next
          App.Workbooks.Close()
        End If
        App.DisplayAlerts = False
        App.Quit()

2. COM Interface Management

Since Microsoft Excel is built upon component architecture, more specifically, using COM+ interfaces, the last code fragment causes several objects in memory to be orphaned. Whether or not we were aware of it, we made use of the global COM infrastructure to create objects in the Excel application.

For example, in the first code listing when a worksheet was added to the active workbook. This line of code created a component instance of the Excel Worksheet class and not only allocated memory for its use but also added a reference in a global counter of such objects.

The result of all this is that when we’re ready to tear down such objects, the reference in our own program can not only be set to ‘Nothing’, but the global reference that was incremented on our behalf must also be decremented. Without this step, you’ll never loosen the grip held onto your Excel objects by the Windows OS. Here is the same bit of code with the addition of a call to the .NET Framework’s ReleaseComObject method for each COM object we’ve created (whether created explicitly or implicitly).

    If Not App.Workbooks Is Nothing Then
        Dim wb As Microsoft.Office.Interop.Excel.Workbook
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet
        For Each wb In App.Workbooks
            For Each ws In wb.Worksheets
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ws)
                ws = Nothing
            Next
            wb.Close(False)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)
            wb = Nothing
        Next
        App.Workbooks.Close()
    End If
    App.DisplayAlerts = False
    App.Quit()

3. Garbage Collection and Memory Management

As a very last step, you can explicitly ask the .NET Framework’s Garbage Collector to reclaim memory that you’ve by now de-allocated. I use the term “ask” here because there is no guarantee that the GC will acknowledge the request. A call to WaitForPendingFinalizers halts the current thread of your program until the GC has had a chance to empty its queue of other requests. This second call is one you may wish to consider more carefully before including.

        GC.Collect()
        GC.WaitForPendingFinalizers()
Sponsored Links