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.