Excel automation: Close event missing

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开发者_Python百科.

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.

---

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.

---

This is not my code, but this worked a treat for me:

https://gist.github.com/jmangelo/301884

Copy paste:

using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace Helpers.Vsto
{
    public sealed class WorkbookClosedMonitor
    {
        internal class CloseRequestInfo
        {
            public CloseRequestInfo(string name, int count)
            {
                this.WorkbookName = name;
                this.WorkbookCount = count;
            }

            public string WorkbookName { get; set; }

            public int WorkbookCount { get; set; }
        }

        public WorkbookClosedMonitor(Excel.Application application)
        {
            if (application == null)
            {
                throw new ArgumentNullException("application");
            }

            this.Application = application;

            this.Application.WorkbookActivate += Application_WorkbookActivate;
            this.Application.WorkbookBeforeClose += Application_WorkbookBeforeClose;
            this.Application.WorkbookDeactivate += Application_WorkbookDeactivate;
        }

        public event EventHandler<WorkbookClosedEventArgs> WorkbookClosed;

        public Excel.Application Application { get; private set; }

        private CloseRequestInfo PendingRequest { get; set; }

        private void Application_WorkbookDeactivate(Excel.Workbook wb)
        {
            if (this.Application.Workbooks.Count == 1)
            {
                // With only one workbook available deactivating means it will be closed
                this.PendingRequest = null;

                this.OnWorkbookClosed(new WorkbookClosedEventArgs(wb.Name));
            }
        }

        private void Application_WorkbookBeforeClose(Excel.Workbook wb, ref bool cancel)
        {
            if (!cancel)
            {
                this.PendingRequest = new CloseRequestInfo(
                    wb.Name, 
                    this.Application.Workbooks.Count);
            }
        }

        private void Application_WorkbookActivate(Excel.Workbook wb)
        {
            // A workbook was closed if a request is pending and the workbook count decreased
            bool wasWorkbookClosed = true
                && this.PendingRequest != null
                && this.Application.Workbooks.Count < this.PendingRequest.WorkbookCount;

            if (wasWorkbookClosed)
            {
                var args = new WorkbookClosedEventArgs(this.PendingRequest.WorkbookName);

                this.PendingRequest = null;

                this.OnWorkbookClosed(args);
            }
            else
            {
                this.PendingRequest = null;
            }
        }

        private void OnWorkbookClosed(WorkbookClosedEventArgs e)
        {
            var handler = this.WorkbookClosed;

            if (handler != null)
            {
                handler(this, e);
            }
        }
    }

    public sealed class WorkbookClosedEventArgs : EventArgs
    {
        internal WorkbookClosedEventArgs(string name)
        {
            this.Name = name;
        }

        public string Name { get; private set; }
    }
}

When I used it I changed it from return the name of the workbook to a reference to the workbook.

---

Schedule a SyncContext action on workbook.Deactivate. This event is fired both when the workbook is closed and when another workbook takes focus.

Normally, in the Deactivate handler you can't check if the workbook is closed or just lost focus, but you can enqueue an action on the SyncContext to execute right after the event. In that action you can check if your workbook is still alive and execute code in case it's not.

Here's an example:

// put a syncContext instance somewhere you can reach it
static SynchronizationContext syncContext = SynchronizationContext.Current ?? new System.Windows.Forms.WindowsFormsSynchronizationContext();

// subscribe to workbook deactivate
workbook.Deactivate += workbook_Deactivate;


[DebuggerHidden]
private void workbook_Deactivate()
{
    // here, the workbook is still alive, but we can schedule
    // an action via the SyncContext which will execute 
    // right after the deactivate event is completed. At that 
    // point, the workbook instance (RCW) will no longer be usable
    // meaning that the workbook has been closed

    syncContext.Post(x =>
    {
        try
        {
            // will throw if workbook is gone
            workbook.Path.ToString();
        }
        catch
        {
            // handle workbook closed
        }
    }, null);
}

---

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.