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:

1. Create an Excel COM Add-in using VB.Net

2. 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

3. 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
   

4. 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
   

5. 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
   

6. 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
   

7. 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.