Excel add-in OnConnection getting called before the workbook is loaded

Question

Does anyone know why, when I load a workbook (double clicking on an .xls file that is already created) my OnConnection gets called on my Add-in before the workbook gets loaded. This causes my ((Microsoft.Office.Interop.Excel.Application)application).ActiveWorkbook to be null.

If I just open Excel from the Excel shortcut, it will load Book1.xls (the default book) and my ActiveWorkbook will be instantiated.

Why is it like this, and what can I do to get my ActiveWorkbook to load before my OnConnection gets called when I load from a file.

I am using Excel 2003 SP3

Answer 1

That's because an Add-In is loaded before the workbook is loaded, and that's when OnConnection fires. That's by design. Also, it only fires once, so if someone were to just open another Workbook using File -> Open, OnConnection isn't going to fire again. Think of OnConnection as a place to do any initialization, like wiring into an event.

Instead, you should handle the WorkbookOpen event to do your logic, and wire it in on OnConnection.

For example:

public void OnConnection(object application, object connectMode, object addInInst, ref Array custom)
{
    var excelApplication = ((Microsoft.Office.Interop.Excel.Application) application);
    //Start listening to the WorkbookOpen event
    excelApplication.WorkbookOpen += WorkbookOpen;
}

private void WorkbookOpen(Workbook wb)
{
    //A workbook was opened. Do your logic here.
}