New Way to Detecting Excel Shutdown Without COM

[Alexander Rickman]

Hi all,

Recently, I needed to detect when the Excel.exe was being shutdown, but I wanted to use something lighter weight than creating a class that derives from ExcelComAddIn and calling ExcelComAddInHelper.LoadComAddIn(...) in the AutoOpen method. The simplest solution I could come up with was sub-classing the main Excel application window via .NET’s NativeWindow class. This should work for all versions of Excel. Below is the code:

ExcelShutdownMonitor

using System; using System.Threading; using System.Windows.Forms; /// <summary> /// Subclasses Excel's application window, filtering window message(s) /// indicating that Excel is shuting down. /// </summary> internal sealed class ExcelShutdownMonitor : NativeWindow, IDisposable { private const int WM_DESTROY = 0x0002; private const int WM_CLOSE = 0x0010; private const int WM_QUIT = 0x0012; private long _disposedCount = 0; private long _closingEventInvokedCount = 0; public event Action ExcelClosing; public ExcelShutdownMonitor(IntPtr excelApplicationHwnd) { AssignHandle(excelApplicationHwnd); } public void Dispose() { Dispose(true); } private void Dispose(bool disposing) { if (Interlocked.Increment(ref _disposedCount) > 1) return; if (disposing) { ExcelClosing = null; GC.SuppressFinalize(this); } base.ReleaseHandle(); } ~ExcelShutdownMonitor() { Dispose(false); } protected override void WndProc(ref Message m) { if (m.Msg == WM_DESTROY || m.Msg == WM_CLOSE || m.Msg == WM_QUIT) { if (Interlocked.Increment(ref _closingEventInvokedCount) == 1) { ExcelClosing?.Invoke(); } } base.WndProc(ref m); } }

Example Usage

using ExcelDna.Integration; using System; public sealed class ExcelDnaAddIn : IExcelAddIn { private ExcelShutdownMonitor _excelShutdownMonitor; public void AutoOpen() { dynamic excelApp = ExcelDnaUtil.Application; _excelShutdownMonitor = new ExcelShutdownMonitor(new IntPtr(excelApp.Hwnd)) _excelShutdownMonitor.ExcelClosing += OnExcelClosing; } public void AutoClose() { } private void OnExcelClosing() { System.Diagnostics.Debug.WriteLine("OnExcelClosing Invoked"); // ***custom logic here*** } }

​

[Govert van Drimmelen]

Hi Alexander,

 

This looks like a promising approach.

Note that, with the code as it is now, this only picks up when the initial workbook closes, and not when Excel will be shutting down.

Separate workbooks live in separate top-level windows, so one needs to keep that in mind.

There is some COM call that give the HWnd associated with a Workbook, as I recall.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/64d1796f-3bdb-45c8-a7a6-ac13889ad5a5n%40googlegroups.com.

[Alexander Rickman]

Govert,

Ah, good catch, thanks! I think the only way to get this working correctly is to update the window handle conditionally. See updated version of `ExcelShutdownMonitor` below: 

**Example Usage** <br>

```C#

using ExcelDna.Integration;

using System;
using System.Threading;
using System.Windows.Forms;

/// <summary>
/// Subclasses Excel's application window, filtering window message(s)
/// indicating that Excel is shuting down.
/// </summary>
internal sealed class ExcelShutdownMonitor : NativeWindow, IDisposable
{
    private const int WM_DESTROY = 0x0002;
    private const int WM_CLOSE = 0x0010;
    private const int WM_QUIT = 0x0012;

    private long _disposedCount = 0;
    private long _closingEventInvokedCount = 0;

    public event Action ExcelClosing;

    public ExcelShutdownMonitor(IntPtr excelApplicationHwnd)
    {

        base.AssignHandle(excelApplicationHwnd);

    }

    public void Dispose()
    {
        Dispose(true);
    }

    private void Dispose(bool disposing)
    {
        if (Interlocked.Increment(ref _disposedCount) > 1)
            return;

        if (disposing)
        {
            ExcelClosing = null;
            GC.SuppressFinalize(this);
        }

        base.ReleaseHandle();
    }

    ~ExcelShutdownMonitor()
    {
        Dispose(false);
    }

    protected override void WndProc(ref Message m)
    {
        if (m.Msg == WM_DESTROY || m.Msg == WM_CLOSE || m.Msg == WM_QUIT)
        {

            dynamic excelApp = ExcelDnaUtil.Application;
            if(excelApp.Workbooks.Count == 1)

            {
                if (Interlocked.Increment(ref _closingEventInvokedCount) == 1)
                {
                    ExcelClosing?.Invoke();
                }
            }

            else
            {
                // if the intial workbook window is closed, but there are
                // additional workbooks, then we need to set up a handle
                // to the next window in the collection, which will become
                // the toplevel window once the current one has been closed
                var activeWindowCaption = excelApp.ActiveWindow.Caption;
                foreach (dynamic window in excelApp.Windows)
                {
                    if (window.Caption != activeWindowCaption)
                    {
                        base.ReleaseHandle();
                        base.AssignHandle(new IntPtr(window.Hwnd));
                        break;
                    }
                }
            }
        }

        base.WndProc(ref m);
    }
}

```

My statement that "This should work for all version of Excel" was also slightly misleading. `ExcelShutdownMonitor` should work regardless of the version, but getting the correct pointer to the top-level application window handle pre-2007 would require the use of Win API functions. 

[Kedar Kulkarni]

Hi Alexander,

Thank you for posting the code and the thought process ... I have a question.

How can we compare the performance of this approach and using OnBeginShutDown from ExcelComAddin. Is there any benchmarking results you could share? Most of the xll addin applications would have some kind of excel ribbon. With Excel Dna if we use ExcelRibbon class, we already have a class that is derived from ExcelComAddIn, unless we have a UDF only xll with no ribbon or some other way which does not include instantiating ExcelRibbon class. So would a project that has a ribbon, would this approach would still be desirable? 

thanks,

Kedar

[Alexander Rickman]

Hi Kedar, 

Glad to contribute.

I don't have any metrics on performance, but since there is no way to subscribe to a subset of window events via the subclassing approach, more will be processed than are necessary. If your add-in has a ribbon (deriving from `ExcelRibbon`) then there is really no need for `ExcelShutdownMonitor`, because you could just override `OnBeginShutdown`. 

My approach was really designed to be used as an alternative to the existing two here, when neither `ExcelComAddin` stand alone nor `ExcelComAddin` as a result of deriving from `ExcelRibbon` are required. 

Thanks, 

Alexander