Excel automation: Close event missing

Question

Another hi all,

I am doing Excel automation via Interop in C#, and I want to be informed when a workbook is closed. However, there is no Close event on the workbook nor a Quit event on the application.

Has anybody done that before? How can I write a piece of code which reacts to the workbook being closed (which is only executed if the workbook is really closed)? Ideally that should happen after closing the workbook, so I can rely on the file to reflect all changes.

Details about what I found so far:

There is a BeforeClose() event, but if there are unsaved changes this event is raised before the user being asked whether to save them, so at the moment I can process the event, I don't have the final file and I cannot release the COM objects, both things that I need to have/do. I do not even know whether the workbook will actually be closed, since the user might choose to abort closing.

Then there is a BeforeSave() event. So, if the user chooses "Yes" to save unsaved changes, then BeforeSave() is executed after BeforeClose(). However, if the user chooses to "Abort", then hits "file->save", the exact same order of events is executed. Further, if the user chooses "No", the BeforeSave() isn't executed at all. The same holds as long as the user doesn't click any of these options.

Answer 1

Can you use both events? On BeforeClose() set a flag, then BeforeSave() see if the flag is set. You would need a way to reset it, though, in case BeforeClose() is triggered and BeforeSave() isn't. Not sure if there is something else that could help with that.

Edit: Looks like you covered this already with "the exact same order of events is executed". But if you can find a way to reset it (another "Cancel" event?) it may work.

Answer 2

I've created a hack using a polling-like approach, and it works:

Given the workbook to observe, I create a thread which periodically tries to find that workbook in the workbooks collection.

(The DisposableCom class is my current solution to properly cleanup COM objects.)

Excel.Application app = wbWorkbook.Application;
string sWorkbookName = wbWorkbook.Name;

Thread overseeWorkbooksThread = new Thread(new ThreadStart(
    delegate()
    {
        bool bOpened = false;

        Excel.Workbooks wbsWorkbooks = app.Workbooks;
        using (new DisposableCom<Excel.Workbooks>(wbsWorkbooks))
        {
            while (true)
            {
                Thread.Sleep(1000);

                if (wbsWorkbooks.ContainsWorkbookProperly(sWorkbookName))
                    bOpened = true;
                else
                    if (bOpened)
                        // Workbook was open, so it has been closed.
                        break;
                    else
                    {
                        // Workbook simply not finished opening, do nothing
                    }
            }

            // Workbook closed
            RunTheCodeToBeRunAfterWorkbookIsClosed();
        }
    }));

overseeWorkbooksThread.Start();

The "ContainsWorkbookProperly" extension methods looks like this:

public static bool ContainsWorkbookProperly(this Excel.Workbooks excelWbs,
    string sWorkbookName)
{
    Excel.Workbook wbTemp = null;
    try
        wbTemp = excelWbs.Item(sWorkbookName);
    catch (Exception)
    {
        // ignore
    }

    if (wbTemp != null)
    {
        new DisposableCom<Excel.Workbook>(wbTemp).Dispose();
        return true;
    }

    return false;
}

Still I would be interested if there is a simpler or better solution.