none
How can I find out, that an Excel-workbook was closed RRS feed

  • Question

  • I am currently writing an Excel "application-level" Add-In using C#.

    I need to link some additional data to a workbook, that I save in the CustomXMLParts section of the workbook. In addition I need to register to some Events.

    When the workbook closes, this data has to be removed from a static List of DataObjects of each open workbook. Furthermore the events have to be unregistered.

    In the AddInBase.Application class an Event called WorkbookBeforeClose is defined.

    But unfortunately after this Event is fired, Excel shows a dialog where the user can specify whether the changes in the workbook shall be saved, or not. This dialog has "Cancel" as a third option. If the user chooses cancel, the workbook will not be closed.

    That's why I can not use this Event for clean-up purposis. Since the clean up will then be performed, even if the workbook stays open.

    What other posibilities do I have to find out, that a workbook did actually close?

    Regards, Helmut

    Tuesday, January 13, 2015 1:34 PM

All replies

  • I am currently writing an Excel "application-level" Add-In using C#.

    I need to link some additional data to a workbook, that I save in the CustomXMLParts section of the workbook. In addition I need to register to some Events.

    When the workbook closes, this data has to be removed from a static List of DataObjects of each open workbook. Furthermore the events have to be unregistered.

    In the AddInBase.Application class an Event called WorkbookBeforeClose is defined.

    But unfortunately after this Event is fired, Excel shows a dialog where the user can specify whether the changes in the workbook shall be saved, or not. This dialog has "Cancel" as a third option. If the user chooses cancel, the workbook will not be closed.

    That's why I can not use this Event for clean-up purposis. Since the clean up will then be performed, even if the workbook stays open.

    What other posibilities do I have to find out, that a workbook did actually close?

    Regards, Helmut

    Tuesday, January 13, 2015 12:27 PM
  • Hello Helmut,

    In addition to the WorkbookBeforeClose event you can try to handle the WorkbookDeactivate event which is fired when any open workbook is deactivated.

    Anyway, VSTO doesn't provide anything for that. I'd suggest asking Excel specific questions on the Excel for Developers forum instead.

    Tuesday, January 13, 2015 1:24 PM
  • Good Idea, thanks, I opend a thread in Excel for Developers now too.

    Performing the cleanup in WorkbookDeactivate does not seem to be a good idea in my eyes: I fear that the workbook can be activated again.

    Tuesday, January 13, 2015 1:41 PM
  • Try to use both these events. For example, if the WorkbookDeactivate event is fired right after the WorkbookBeforeClose - the workbook is going to be closed.
    Tuesday, January 13, 2015 1:43 PM
  • Good idea! The problem is, that the user could leave the "save dialog" open to go and get some coffee. Then "right after" might be quite a long time. I would prefer to use a timer that supervises the workbook-collection frequently, to see that all workbooks are still up and running. But I thought there must be a propper way. (I do not think I am asking for something strange: Beeing informed that a workbook was closed is something fundamental in my eyes!)

    Cheers Helmut

    Tuesday, January 13, 2015 1:56 PM
  • The Excel object model doesn't provide such events. I have described a possible workaround above.
    Tuesday, January 13, 2015 2:03 PM
  • Hi Caer_Dalben,
     
    If this were VBA I would know how to handle this. From VBA I normally schedule
    a routine to run after the beforeclose event that:
     
    - Checks if the file was really closed
    - If so, cleans up.
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Tuesday, January 13, 2015 3:22 PM
  • Hi Caer_Dalben,

    As Eugene mentioned that there is no such API we can handle for this scenario.  I suggest that you submit the feedback from link below:
    Submit Feedback - Microsoft Office

    In addition to Jan Karel Pieterse, I think you also can create a new thread in Workbook.BeforeClose Event to check whether specific workbook still exists in the Workbooks collection every seconds in a application-level add-in.

    Hope it is helpful.

    Regards & Fei


    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.

    Friday, January 16, 2015 8:09 AM
    Moderator
  • Hi Fei,

    thanks for your suggestion. I thought about starting a thread (or a timer) already, but there is no way to find out, that the close-operation was cancelled. So this thread can not be stopped at a reasonable time.I( I already mentioned, that the user could leave the dialog box open for an indeterminate period.)

    That's why the only solution in my eyes is, to have a thread or timer running during the whole lifetime of the AddIn, supervising all open workbooks.

    This can be done, but is quite an effort for such an obvious task.

    As you suggested, I will submit this thread to Microsoft, since I think this is defenitely missing in the framework.

    Friday, January 16, 2015 10:04 AM