AfterClose event


A common theme from customers I work with who are developing some type of document management system is that they need to know when a file is closed. Completely closed.

The BeforeSave events let you know just before the file closed and allows you the change to cancel the event, but there is no AfterClose event.Common scenarios are they need to be able to grab the file at rest (after close), move it somewhere else, change some data using OpenXML, or perform some other operation against the file. And I have seen some convoluted workarounds to get to that point as well. Annoyed

While there is not an AfterClose event, you can create one. Hot smile The following code demonstrates it and the comments explain how/why…

private void ThisAddIn_Startup(object sender, EventArgs e)
{
    // hook to the Before Save Event
    Application.WorkbookBeforeClose += new
        Excel.AppEvents_WorkbookBeforeCloseEventHandler(
            Application_WorkbookBeforeClose);
}

/// <summary>
/// The BeforeClose event we attach to. The only event
/// exposed by Excel to us to determin that the file is
/// about to close
/// </summary>
/// <param name="Wb">The Workbook object being closed</param>
/// <param name="Cancel">If true, closing is stopped</param>
void Application_WorkbookBeforeClose(Excel.Workbook Wb,
                                     ref bool Cancel)
{
    // because this event fires before the close we will
    // do whatever work we need to do here, such as
    // prompting the user, pre-work or other items.

    // <– your code here –>
    // be sure if needed to set Cancel=true here if
    // needed…
    // <– your code here –>

    // now we check to see if cancelled…
    if (Cancel == false)
    {
        // ok – not cancelled – so we spawn a thread
        // but first…
        // we cannot use the Wb.FullName inside the delegate
        // because it will be destroyed by the time the thread
        // fires off. So we put it into a variable and pass
        // that value with the delegate.
        string wbFullPath = Wb.FullName;
        // start new inline thread
        new Thread(new ThreadStart(delegate()
        {
            // we will call back to the Handle_AfterClose
            // method and pass it the name of the workbook.
            Handle_AfterClose(wbFullPath);
            // NOTE: Because Excel takes the next action to
            // close the file, this threads immediate call
            // back into the add-in will not occur until
            // Excel has compelted the operation.
        })).Start();
    }
}

/// <summary>
/// This is the callback method ("event") we use to determine
/// the point after which our file has been closed.
/// </summary>
/// <param name="fn">The full path and name of the file
///                  closed</param>
private void Handle_AfterClose(string fn)
{
    // display a simple message at this point
    MessageBox.Show(fn + " has been closed!");
}

One thought on “AfterClose event

  1. Thanks again for your useful tips! I’ve used something similar right now
    with a System.Windows.Forms.Timer to finally get my AfterSave event.
    A ‘simple’ thread won’t do the trick like in your word’s AfterSave approach.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s