Best way to manage the ActiveWorkbook in a VSTO Excel Addin application

Question

I've developed a rather intensive Excel Addin (VTSO based) that creates an in-memory datatable from database query, and then binds this to ListObject. There are a bunch of other functions available when this ListObject is created (including event handlers that update calculations in the datatable, and custom task panes that popout to show additional information about a cell).

The Addin works well, although I'm getting issues when users open other Excel workbooks or additional instances of Excel to multi-task on work.

What's the best way to manage the state of this particular application? Users should only ever work on one copy of this workbook at a time, however the flexibility of Excel makes it difficult to manage.

How do others manage this? I was thinking of creating a GUID when a workbook is created, and then testing for this whenever code is called to make sure that the active workbook is the correct workbook. Or is there a better way?

Answer 1

I believe that you shouldn't force users to only work with a single workbook.

For excel 2013 and higher, you should maintain a reference to all workbooks that are open (in a dictionary for instance). Every time a new workbook is activate/deactivate you need to update those references. You can use the integer Globals.ThisAddIn.Application.Hwnd that correspond to the unique top level windows handle as a key for your dictionary. You can attach each key to a class that correspond to the state of your application, every times the workbook activate/deactivate event is fired you need to update those states.

In other words, you should attach the setting of your application (database connection ...) to workbooks thanks to Globals.ThisAddIn.Application.Hwnd, no need to use a GUID.

Also see this answer for reference.