none
Excel AddIn: How to catch the appropriate event when a workbook goes away? RRS feed

  • Question

  • My AddIn keeps a dictionary (key: any open workbook, value: something of importance). At the time of a workbook disappearance I need it to remove itself from that dictionary and do some housecleaning.

    The closest event I have found is excel.WorkbookBeforeClose() which has a problem. At the last minute, the user may decide to click on Cancel and the workbook will stay around. I need the equivalent of excel.WorkbookAfterClose().

    Suggestions?

    TIA


    Thursday, June 14, 2018 3:23 PM

Answers

  • See the solution here:

    excelApp.WorkbookBeforeClose += excel_WorkbookBeforeClose;
    
    void excel_WorkbookBeforeClose(Workbook Wb, ref bool Cancel)
    {
    }
    

    The implementation of that event happens to come with an Ok and a Cancel option/button.

    Through those, it is possible to undo whatever is needed, when the user selects "Cancel".

    Thx


    Tuesday, June 19, 2018 8:14 PM

All replies

  • Maybe start a timer in the WorkbookBeforeClose Event.  When the timer fires you'll be out of the event handler and you can check to see if the workbook of interest is still present in the Application object's Workbooks collection.
    Thursday, June 14, 2018 3:47 PM
  • Maybe start a timer in the WorkbookBeforeClose Event.  When the timer fires you'll be out of the event handler and you can check to see if the workbook of interest is still present in the Application object's Workbooks collection.

    Thanks for the tip, but that is too risky. Our environment is quite messy, and the grand goal of my project is to reduce our dependency on Excel. We have huge spreadsheets, with colors, formulas, inconsistent data, etc. I want to simplify the environment, dealing with a timer would add a layer of complexity.

    Back to the subject of the post: since the objective is to keep my list of open workbooks synced with the one that Excel keeps, perhaps I should scan the Excel "roster", compare it with my own and as soon as I notice: "Aha! This workbook is MIA!", I remove it from my private list (dictionary).

    I think I can find a spot in the code right before the contents of the dictionary become relevant, in which to perform the syncing operation mentioned above.

    Thx


    Thursday, June 14, 2018 4:52 PM
  • Hello Travis Banger,

    One you close a workbook while there are multiple workbooks opened. One of the remain workbook will be activate and so the WorkbookActivate will trigger.

    I would suggest you create a global bool variable. Its default value is false and we will set it as true. And then in WorkbookActivate event, we could check the variable. If it is true, then you could loop through to check if you could use the key to get a workbook. If you failed to get the workbook, that means it is closed and then you could clean it from the dictionary.

    Code like this,

            bool needCheckFlag=false;
            private void XlApplication_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
            {
                needCheckFlag = true;
            }
    
            
            private void XlApplication_WorkbookActivate(Excel.Workbook Wb)
            {
                if (needCheckFlag) {
                    //loop through the dictionary to check if you could use the key to get a workbook from current application
                    //if you failed, that means the workbook is closed and then you could clean it from the dictionary
    
                    //do the check again until last time you close a workbook
                    needCheckFlag = false;
                }
            }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 15, 2018 8:24 AM
  • Hello Travis Banger,

    What's the state of the current thread? Is your original issue resolved? If it does, I would suggest you mark the helpful reply to close the thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 18, 2018 8:04 AM
  • Hi Terry:

    I believe I found the answer, but have to test it first. If it works, I will post it.

    Thanks.

    Monday, June 18, 2018 10:07 AM
  • Hello Travis Banger,

    If you have any updates for the issue, please feel free to let us know.

    By the way, I'm also interesting about the current state of below thread. Do you have any updates for it?

    https://social.msdn.microsoft.com/Forums/en-US/f4b0007e-66a8-4a4f-b750-d0f9dbbba6dd/what-is-the-proper-way-to-use-interop-in-a-regular-non-addin-non-office-application?forum=exceldev

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 19, 2018 6:51 AM
  • See the solution here:

    excelApp.WorkbookBeforeClose += excel_WorkbookBeforeClose;
    
    void excel_WorkbookBeforeClose(Workbook Wb, ref bool Cancel)
    {
    }
    

    The implementation of that event happens to come with an Ok and a Cancel option/button.

    Through those, it is possible to undo whatever is needed, when the user selects "Cancel".

    Thx


    Tuesday, June 19, 2018 8:14 PM