Excel: How do you implement Application_Quit event in Excel/how do you intercept Excel application Quit
We know Word Object model provides an event handler called Application_Quit, which will be triggered when the application quits, but we don’t have any such events for Excel (even for PowerPoint and some other Office applications). In order to workaround this issue, first thing that will come in our mind is to use an Office COM add-in and implement OnBeginShutdown method (which “IDTExtensibility2” interface exposes). Moreover, this can be implemented for any/multiple Office applications as well. Otherwise, we can even use ThisAddIn_Shutdown method that VSTO Application level add-in provides.
However, all these methods can run some code when the application is being shut down and they do not allow us to control the quitting of the application. Let us assume a scenario, what if we need to intercept the application quit and need to decide whether to quit the application or not based on certain criteria/business rules.
Here I come with an option to use the old style of processing Windows messages that Office applications receive. I have described the steps below to implement this thought for Excel using VB.Net COM Add-in in much simpler way.
Here are the steps:
Create an Excel COM Add-in using VB.Net
In order to process the windows messages that an Office application receives, we need to inherit the Connect class (Connect.vb) of the COM add-in from, System.Windows.Forms.NativeWindow class. This class provides an overridable method called, WndProc() , that passes Windows message as an argument. We can make use of these messages to implement, as we want. To inherit the NativeWindow class in our Connect class, please make sure that you have added reference for System.Windows.Forms
Then, add the following Using statements in the Connect.cs module,
Imports Extensibility Imports System.Runtime.InteropServices Imports System.Windows.Forms Imports Excel = Microsoft.Office.Interop.Excel
Declare the following constant. It is the value for “Window Close” message, which will be passed by Excel when user clicks close button
Const WM_CLOSE As Integer = 16
Add the following code in the OnConnection method of the Connect class. The main purpose of using this method is to pass the Excel application’s handle to NativeWindow class so that it can process the messages that the application receives through the WndProc method
Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection 'Type cast the application object to Excel application class xlApp = DirectCast(application, Excel.Application) 'Pass the Excel application handle to NativeWindow class in order to process messages that Excel receives MyBase.AssignHandle(xlApp.Hwnd) 'To display a message box MessageBox.Show("Excel quit detection add-in started!") End Sub
Copy the following WndProc method implementation to trap the close action in Excel
Protected Overrides Sub WndProc(ByRef m As System.Windows.Forms.Message) 'Check if the Window handle passed by this procedure is for Excel application and the message is to indicate closing Excel application If m.HWnd = xlApp.Hwnd And m.Msg = WM_CLOSE Then Dim wb As Excel.Workbook 'Prompt Message box to close Excel or not Dim res As DialogResult = MessageBox.Show("You are quitting Excel. Do you want to save changes for the workbooks opened?", "Excel Quit", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) 'If the user response is "Yes" for the message box, then loop through all the workbooks, save and close it If res = DialogResult.Yes Then For Each wb In xlApp.Workbooks wb.Close(True) Next MyBase.WndProc(m) 'process message as default ElseIf res = DialogResult.No Then 'If no, Loop through all the workbooks and close it, but don't save For Each wb In xlApp.Workbooks wb.Close(False) Next MyBase.WndProc(m) 'process message as default End If 'If none of the above criteria is not satisfied, Excel will not quit Else MyBase.WndProc(m) 'process message as default End If End Sub
Build the add-in and run it. Now, Excel will prompt with a message box, when user attempts to close Excel application by either clicking “X” button or by going to File menu-->Exit or by pressing Alt+F4.