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.ApplicationoApp.Workbooks.Add()oApp.Visible = True'For getting handle for the actual Excel window that we launched Dim iOurWindowHandle As Integer = oApp.HwndDim XLProcess As Process = Nothing'This will hold the Excel process launched by our applicationDim 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 IfNextIf ourXLProcess Is Nothing ThenMessageBox.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 = TrueWhile fXLPresent ourXLProcess.Refresh()'HasExited check added scenarios where someone kills Excel process (from task manager)If (ourXLProcess.HasExited = True) Then fXLPresent = False 'Check the MainWindowHandleElseIf (ourXLProcess.MainWindowHandle = IntPtr.Zero) Then fXLPresent = FalseEnd IfIf fXLPresent = False Then MessageBox.Show("Excel has quit") Exit WhileEnd IfSystem.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”