Excel: How do you implement Application_Quit event in Excel – In External automation scenario

You may have come across this blog which demonstrates how you can capture Excel’s quit event from an Add-In (in process). This post will be about capturing Excel’s quit event when you are externally automating Excel.

Consider this situation – You have an application that automates Excel and presents Excel window to the user for further manipulation, and probably also captures other Excel events. Now, you may want to execute code after the user has done what he wanted to do and close/quit Excel.

To cater to this need of developers I am presenting this technique, here is how it goes –

After launching Excel process, we get the Excel window handle (Excel Object model exposes this as Application.hWnd). We then traverse through all the (Excel) processes to get the Process object for the Excel that we launched.We then wait till the Process.MainWindowHandle property is zero. Since our application is holding reference(s) to Excel object(s) we cannot say for certain that Excel process will be removed from memory when user closes Excel, and hence the need to look at MainWindowHandle property.

There may be times when Excel is abruptly closed, if someone killed the Excel process. To cater to this, you can add an additional check for Process.hasExited property.

Here are the steps to demonstrate this technique –

1) Create a Windows forms application in VB

2) Add a button in the designer and name it as “Launch and watch excel for quit”

3) Add COM reference to the “Microsoft Excel 12.0 Object Library”

4) Add the imports statement in the Form1.vb file

 Imports Excel = Microsoft.Office.Interop.Excel

5) Declare an application object for Excel

 Dim oApp As Excel.Application

6) In the click event handler for the button added in step 2 write the following code :

 oApp = New Excel.Application
oApp.Workbooks.Add()
oApp.Visible = True

'For getting handle for the actual Excel window that we launched 
Dim iOurWindowHandle As Integer = oApp.Hwnd

Dim XLProcess As Process = Nothing

'This will hold the Excel process launched by our application
Dim ourXLProcess As Process = Nothing

'For getting the handle to our specific Excel window (the one launched by us)
For Each XLProcess In Process.GetProcessesByName("Excel")
    If (iOurWindowHandle = XLProcess.MainWindowHandle.ToInt32()) Then
        ourXLProcess = XLProcess
        Exit For
    End If
Next

If ourXLProcess Is Nothing Then
MessageBox.Show("Could not get hold of the Excel process that was launched")
End If


'Now, we will keep a watch over the Excel process launched by us
'Probably can go in to a separate thread. This currently is a tight loop, blocking the Form UI till Excel is closed.
Dim fXLPresent As Boolean = True

While fXLPresent
    ourXLProcess.Refresh()

'HasExited check added scenarios where someone kills Excel process (from task manager)
If (ourXLProcess.HasExited = True) Then
        fXLPresent = False
        'Check the MainWindowHandle
ElseIf (ourXLProcess.MainWindowHandle = IntPtr.Zero) Then
        fXLPresent = False
End If

If fXLPresent = False Then
        MessageBox.Show("Excel has quit")
        Exit While
End If

System.Threading.Thread.Sleep(2000)
End While

 

7) Build the project and run it. This will launch a Windows form with a button. On clicking the button the Excel is launched. If you click on the close button on Excel window or kill the process from the task manager we are presented with the MessageBox saying “Excel has quit”