none
Workbook.Shutdown event not called on reopening of an already open workbook RRS feed

  • Question

  • Hi,

    I have an Excel application level add-in (for Excel 2010 and 2013) written in c#, VSTO, on .NET Framework v4. When a workbook gets opened into Excel I store a reference to the XLS.Workbook host-item in my code. When the workbook is closed in Excel I trap the Workbook.Shutdown() event and dispose off the host item. All works just fine but for one specific scenario...

    i.e., when I open a workbook into Excel and then 'reopen' that same workbook by double-clicking on the file in the File Explorer! By doing so, the currently open workbook gets closed in Excel and the same workbook is then reopened (can be clearly seen on screen, workbook disappears and reopens anew). Now this would be OK if I could trap the Shutdown event, but it is never called!? Neither are the Workbook.BeforeClose() or the Workbook.Dispose() events, despite the fact that Excel clearly closes the workbook.

    Since I cannot trap the reopening event of the workbook I am left with one XLS.Workbook reference that exists in a limbo state. Help please...

    Many thanks in advance! /T

    Monday, October 27, 2014 5:49 PM

All replies

  • Hello,

    Did you have a chance to check out the Workbooks property of the Application class?

    What code do you use for subscribing and handling the BeforeClose event of the Workbook class?

    Make sure that the source workbook object is not swiped by the garbage collector.

    Monday, October 27, 2014 5:52 PM
  • Hi,

    thanks a lot for the swift reply. I subscribe to the Workbook.Shutdown() event after I trap the Application.WorkbookOpen() event like that,

    this.fBook.Shutdown += newSystem.EventHandler(OnBookShutdown);

     ... and when a workbook is closed I can trap this event "at all times", except in this one very specific scenario --> when I open an Excel file from disk (the add-in subscribes to the above event correctly) and then double-click on the same file on disk again (without having closed it before). Excel then performs a "reopen" of that file, i.e. it closes it and opens it in one shot.

    Only when I do that, I am not informed about Excel closing the workbook, i.e. neither the Workbook.Shutdown(), Workbook.BeforeClose() or the Application.WorkbookBeforeClose() events are called when Excel closes the book. When it reopens the workbook the entry events are called correctly (Application.WorkbookActivate() and Application.WorkbookOpen()), but at that point I can of course not recognize my previous reference to the Workbook host-item any longer...

    The Workbooks properties' count is 1 before I perform the reopen of the file and after as well (as it should be). Excel handles everything correctly internally, i.e. it closes the file and reopens it (if I modify the file before reopening a dialog comes up that warns about that this file will be closed and reopened and that my changes will be lost). The only thing it does NOT do is to fire the events notifying VSTO that the current workbook is about to be closed...?

    My object is not swiped by GC (global pointer in the ThisAddin class),... rather unfortunately so since I actually want to get rid of it, but in a controlled fashion :-)

    Many thanks again for your help! /T

    Made a simple application add-in in VS2013 for a Excel 2013 add-in .NET 4.5.1 containing only the few lines listed here:

        public partial class ThisAddIn
        {
            private XLS.Workbook fBook = null;
    
    
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                Application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(Application_WorkbookOpen);
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
                Application.WorkbookOpen -= Application_WorkbookOpen;
            }
    
            private void Application_WorkbookOpen(Excel.Workbook Wb)
            {
                fBook = Globals.Factory.GetVstoObject(Wb);
                fBook.Shutdown += new System.EventHandler(OnBookShutdown);
            }
    
            private void OnBookShutdown(object sender, System.EventArgs e)
            {
                fBook.Dispose();
                fBook = null;
            }
    

    Do the following:

    1. Create an Excel 2013 add-in project and paste the above code into ThisAddin.cs.

    2. Breakpoint in Application_WorkbookOpen and OnBookShutdown. F5 and open a workbook into Excel 2013.

    3. Stops correctly in Application_WorkbookOpen() and creates the workbook host-item and assigns the shutdown event.

    4. Close the workbook in Excel. Stops correctly in OnBookShutdown and cleans up.

    5. Open the workbook into Excel again and modify any cell.

    6. Reopen the workbook by a double-click on the file on disk (this is important since if you open it via the file menu, Excel does nothing)(NOTE, do NOT close the workbook in Excel before reopening, Excel will ask you to confirm that you want to reload the workbook and that you will loose your latest changes).

    7. The workbook is closed in Excel and reopened in one shot. OnBookShutdown is NOT called even tough the book has been closed by Excel...

    8. Now close Excel! OnBookShutdown is now called TWICE. The first time for the latest instance of the workbook and a second time for the first (already closed instance).

    • Edited by tho_BA Monday, October 27, 2014 8:01 PM
    Monday, October 27, 2014 7:15 PM
  • Hi tho_BA,

    Based on my understanding, Excel Application will use the same workbook object in memory when reopening a workbook. If so, we could use the code below as a workaround.

            private Workbook fBook = null;
    
            private string currentWorkbookName = string.Empty;
    
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                Application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(Application_WorkbookOpen);
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
                Application.WorkbookOpen -= Application_WorkbookOpen;
            }
    
            private void Application_WorkbookOpen(Excel.Workbook Wb)
            {
                // handle reopen operation
                if (!string.IsNullOrEmpty(currentWorkbookName) && Wb.Name == currentWorkbookName)
                {
                    FreeWorkbookObject();
    
                    fBook = Globals.Factory.GetVstoObject(Wb);
                }
                else
                {
                    fBook = Globals.Factory.GetVstoObject(Wb);
    
                    fBook.Shutdown += new System.EventHandler(OnBookShutdown);
                }
    
                currentWorkbookName = fBook.Name;
            }
    
            private void FreeWorkbookObject()
            {
                fBook.Dispose();
                fBook = null;
                currentWorkbookName = string.Empty;
            }
    
            private void OnBookShutdown(object sender, System.EventArgs e)
            {
                FreeWorkbookObject();
            }

    Hope it will help.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 3, 2014 5:46 AM
    Moderator
  • Hi Jeffrey,

    thanks a lot for you reply. Currently, I check the FullName property of the VSTO workbook object. On Application.WorkbookOpen I loop all my workbook host-items and access their FullName property. If its a workbook that has been reopened, the host-item is invalid and it will raise. However, this I feel is a bad workaround for a problem that inherently exists in VSTO so I have posted this as a bug some time ago on Connect...

    "https://connect.microsoft.com/VisualStudio/feedbackdetail/view/1015377/excel-vsto-workbook-shutdown-event-not-called-on-reopening-of-an-open-workbook"

    Sorry for not updating this post as well, herewith done! I posted the problem and the (if event notification would be implemented properly not needed) workaround there instead.

    Best regards /T.

    Thursday, November 6, 2014 7:58 AM