DevCity.NET - http://devcity.net
50 Ways to Kill Excel
http://devcity.net/Articles/239/1/article.aspx
Scott Rutherford
Scott Rutherford is a consultant and .NET developer who has developed Enterprise business applications in many fields including Restaurant, Market Research, Automotive, and Analytical Chemistry. He is an Microsoft Certified Professional with the .NET Platform. 
by Scott Rutherford
Published on 6/14/2006
 
For anyone using Excel automation in .NET or ASP.NET applications, you'll find several answers to the age old question haunting many newsgroups, blogs and forums:  "How do I kill Excel?".  After outlining how to configure and start an Office app within your .NET desktop or ASP.NET application, this article shows you how to properly dismantle and close the specific instance of Excel that you had started.  For the interested student this article will explore several important Windows concepts that apply well beyond the usage of Excel:  including COM interop/config./management, the .NET Process class, and how to use “PInvoke” to call Windows API methods not implemented in the .NET Frameworks.

Overview

"How do I kill Excel" is a very common question asked in development forums and answered hundreds of times. Web developers often ask about dozens of Excel.exe processes stacking up in Task Manager. A google search for "kill excel" yields nearly 5,000,000 hits. Oddly the question is also answered in several different ways—each guru mentioning his or her pet solution to the problem. In this article, we won't see 50 ways, but we'll investigate various possibilities. We'll politely ask Excel to quit, we'll close and de-reference COM interfaces, manage garbage collection (GC), and ultimately grab a process that just refuses to go -- and kill it.

The most complex situation is undoubtedly in ASP.NET where the server process is running Excel in one of many security scenarios (ASPNET user, IUser, impersonated end user, etc.); and without a desktop user session. Hence we'll use this as our illustration to cover all bases. But keep in mind that you may need to simplify the solutions to your particular needs. For example, you may wish to kill all running instances of Excel, which is a far simpler problem than targeting a particular process.

If you're already knee-deep in this problem, and only interested in force-killing Excel, then skip to page 4 now.

For the interested student this article will explore several important Windows concepts that apply well beyond the Excel application:
COM interop/config./management, the .NET Process class, and how to use “PInvoke” to call Windows API methods not implemented in the .NET Frameworks.
There are certainly many thorny patches to walk through when involving Office automation in any of your applications (Fig. 1). This article really only attempts to address one of them, namely how to close the Office app from your calling app.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when run in this environment.

Besides the technical problems, you must also consider the feasibility of such a design with respect to licensing. Current licensing guidelines prevent Office Applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA).

-- taken from http://support.microsoft.com/kb/257757


Figure 1: Note to self ... Office doesn't work

 

Excel Inception

1. Open Excel

Before we can go on about stopping Excel, we’ll quickly look at how to start Excel. If this is nothing new you may move on to the next page. However, you need to be aware of one line that may not appear in your code: note where the Caption property of the Excel application is set. It is set to a unique identifier by creating a GUID string.

    Public Function OpenExcel(ByVal Interact As Boolean) As _
      Microsoft.Office.Interop.Excel.ApplicationClass
        Dim App As Microsoft.Office.Interop.Excel.ApplicationClass
        App = New Microsoft.Office.Interop.Excel.ApplicationClass
        App.DisplayAlerts = Interact
        App.Interactive = Interact
        App.Visible = Interact
        ' uniquely mark this instance of Excel
        App.Caption = System.Guid.NewGuid.ToString.ToUpper
        Return App
    End Function

The above code listing includes everything that is required to make an instance of the Excel Application in VB.NET.
If you need to instantiate a particular version of Excel, see “Achieving Backward Compatibility with .NET Interop: Excel as Case Study” first.

Depending on your platform, there may be additional steps required to get Excel running. One of the most common issues that will come up in an ASP.NET environment is listed below, along with the solution steps.

Common Error:

The single most common issue encountered when instantiating an automation object on the server in an ASP.NET environment looks something like this:

BEGIN
ERROR on Excel open [System.UnauthorizedAccessException]: Access is denied.
... DONE!

System Application Event Log reports:
Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
User: MACHINENAME\ASPNET
Computer: MACHINENAME
Description:
The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {00024500-0000-0000-C000-000000000046} to the user MACHINENAME\ASPNET SID (S-1-5-21-##########-##########-#########-####). This security permission can be modified using the Component Services administrative tool.

Solution:

  1. Open “COM Config.” from mmc console (Control Panel | Administrative Tools | Component Services). For Windows 2000, run comcnfg from a command line.
  2. Find "Microsoft Excel Application" {00024500-0000-0000-C000-000000000046}
  3. Right-click and select Properties | Security tab
  4. Under "Launch and Activation Permissions", select "Customize" and click "Edit" button
  5. Add ASPNET Machine Account and allow "Local Launch" and "Local Activation"

2. The MAIN Sub

The following code listing shows what a simple Main() routine would look like for a console application that (a) creates an Excel automation object, (b) calls a couple procedures on it, and then (c) closes. If you are writing in ASP.NET, you can do something similar in the Page_Load method [please understand that this goes into your server code and thus will launch Excel on the server]. Note that the implementation of the CloseExcel() method will comprise the remainder of this article.

    Sub Main()
        Dim myExcel As Microsoft.Office.Interop.Excel.ApplicationClass
        Dim sh As Microsoft.Office.Interop.Excel.Worksheet
        myExcel = OpenExcel(True)
        sh = myExcel.Workbooks.Add().ActiveSheet
        sh.Range("A1:A1").Value = "Hello World"
        Console.WriteLine("... hit ENTER to close:")
        Console.ReadLine()
        CloseExcel(myExcel, True)
        Console.WriteLine("... hit ENTER to exit:")
        Console.ReadLine()
    End Sub
 

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()
 

Excel Force-Kill

Kill the Process

In most cases ... you need to kill a particular instance of Excel

There will be times that you get past all lines of code that we’ve covered thus far and your Excel process is still running. It is at this point most developers become totally confounded. Hence the deluge of questions and answers I mentioned in the overview at the beginning of this article. If you are one of these and you’ve read this far because nothing mentioned so far solves your problem, and you’ve begun to feel the murderous rage towards Excel… you are justified, and here is how to indulge that feeling. It is now time to play hardball with Excel.

There are situations where surgical precision is not required. In these cases, you can make use of the .NET Process class.

    Sub KillAllExcels()
        Dim proc As System.Diagnostics.Process
        For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
            proc.Kill()
        Next
    End Sub

In most cases, however, you need to kill a particular instance of Excel. This is particularly important in a web context where you could have several users with Excel processes running simultaneously. Unfortunately the current implementation of the .NET Process class is useless here. Any granular use of a process in .NET, such as getting its PID or checking its window properties, makes use of performance counters. This is difficult to set up and nearly impossible to allow (in terms of NTFS and security). While the Process class was helpful for knocking off any unidentified Excel process in the code above, it is not good for identifying instances. For this, we need to turn to the Windows API.

It may come as a surprise to you that the best way to identify a process in Windows is to identify its windows. For this reason, you might recall, we used a GUID to label the Caption of our Excel application when it was created in the OpenExcel() method, above:

        App.Caption = System.Guid.NewGuid.ToString.ToUpper

Excel versions 10 and later provide an API call of their own that returns the windows handle for the application’s main window:

Dim sVer As String = App.Version        
Dim iHandle As IntPtr = IntPtr.Zero
        If Val(sVer) >= 10 Then iHandle = New IntPtr(CType(App.Parent.Hwnd, Integer))

Otherwise, the task is now to find this window. Once the main window is found, we’ll extrapolate to the process that owns the window, and kill it. “PInvoke” is the way to call static DLLs from managed code. In order to complete these steps, we’ll define hooks to (i.e. “we’ll PInvoke”) the following OS methods, respectively: FindWindow(), GetWindowThreadProcessID, EndTask().

We’ll also use SetLastError() to zero-out the error state of our application so that we don’t get confused by errors that may have occurred previously. The code listing here uses the PInvoke mechanism to define functions in your VB.NET that can now be called inside your code as if they were located there:

    Declare Function EndTask Lib "user32.dll" (ByVal hWnd As IntPtr) As Integer
    Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _
           (ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr
    Declare Function GetWindowThreadProcessId Lib "user32.dll" _
           (ByVal hWnd As IntPtr, ByRef lpdwProcessId As Integer) As Integer
    Declare Function SetLastError Lib "kernel32.dll" (ByVal dwErrCode As Integer) As IntPtr
• Note: a helpful web resource is www.PInvoke.net; a wiki repository of available API calls with sample code snippets. Several entries were added and modified from code written for this article.

Our algorithm to find and kill the target process is as follows:

IF windowhandle is NOT known
     THEN SET windowhandle = FindWindow(uniqueid)

IF processid is NOT known
     THEN SET processid = GetWindowThreadProcessID(windowhandle)

IF processid is NOT known
     CALL EndTask(windowhandle)
Else
     Instantiate .NET Process object with processid
     Close main window of this process
     IF still alive
          THEN Kill this process

The VB.NET code will look something like this:

    Public Sub EnsureProcessKilled(ByVal MainWindowHandle As IntPtr, ByVal Caption As String)
        SetLastError(0)
        ' for Excel versions <10, this won't be set yet
        If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then _
      MainWindowHandle = FindWindow(Nothing, Caption)
        If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then _
      Exit Sub ' at this point, presume the window has been closed.
        Dim iRes, iProcID As Integer
        iRes = GetWindowThreadProcessId(MainWindowHandle, iProcID)
        If iProcID = 0 Then ' can’t get Process ID
            If EndTask(MainWindowHandle) <> 0 Then Exit Sub ' success
            Throw New ApplicationException("Failed to close.")
        End If
        Dim proc As System.Diagnostics.Process
        proc = System.Diagnostics.Process.GetProcessById(ProcessID)
        proc.CloseMainWindow()
        proc.Refresh()
        If proc.HasExited Then Exit Sub
        proc.Kill()
    End Sub

... and that is it.