In a C# XLL (Excel add-in using ExcelDNA), how do I detect when a Workbook is closing?

Question

I have an XLL built using ExcelDNA in C#, that contains some UDFs for calculations to use in plain XLSX workbooks. There are some actions I want the add-in to perform on the BeforeClose event in the Workbooks themselves, without having to change the Workbooks to XLSM files with their own event handlers. How do I get my XLL to catch the "workbook is closing" event from the Excel application, and then grab a reference to that Workbook (actually just its filename will do), so that I can run some actions on the file before it actually closes?

Answer 1

I found an alternative to this just using Microsoft.Office.Interop.Excel (which I am already using for some very light interaction with Excel anyway). I just defined a method for the event handler:

public void Event_Application_WorkbookBeforeClose(xl.Workbook workbook, ref bool Cancel)
{ // Excel BeforeClose event handler - use to tidy up
    // Do some stuff
}

Then I use the following in one of my bits of code that runs pretty early (after loading the XLL):

if (!eventHandlerLoaded) { app.WorkbookBeforeClose += Event_Application_WorkbookBeforeClose; eventHandlerLoaded = true; }

Where I use a static bool "eventHandlerLoaded" to check if I have already appended the event handler yet or not.

Turns out it was pretty simple.

Answer 2

Install the NetOffice Framework via nuget:

https://netoffice.io/

Now in your add-in's AutoOpen you can create an Application object that gives you access to all the COM stuff, including events.

I'm working in F#, not C#, but here's how it looks for me:

open NetOffice.ExcelApi
open NetOffice.ExcelApi.Enums

module AddIn =
    let mutable private myApp : Application = null

    let beforeClose = Application_WorkbookBeforeCloseEventHandler (fun sender e -> 
        // do something here - sender is the workbook
        ())

    // I call this from my type I define that implements IExcelAddIn
    let autoOpen() = 
        myApp <- new Application (null, ExcelDnaUtil.Application)
        myApp.add_WorkbookBeforeCloseEvent beforeClose