Office wide After Save As Event (and tangent on extension methods and lambdas in Office code)

First off, I am way behind on my blogging. I actually owe a few blog entries to some folks that I will be getting around to. Life and work has been busy, complicated, not quite as balanced as I would like. Disappointed smile But this one issue has recently come up and is directly customer focused, therefore it gets the priority.

I was recently asked how to handle an After Save As scenario exactly the same in each application – as closely as possible. And only the Save As scenario. This is the scenario in which the user s saving a document for the first time or the user is choosing to same the same file with a different name and/or location.

So, wild tangent time… Hot smile  If you have been following my blogs for a while you will find out two things I like to do, call them programming style:

  1. Use extension methods
  2. Use inline Lambda expressions

This example today is no different. However, I recently got into a philosophical discussion on why I take these two approaches and WHY I think why you should too.

<rant>
Extension methods allow you to encapsulate a lot of code, allow for multiple re-use in other projects and once tested and vetted, keep the root entry points of your code (usually event methods or ribbon button clicks) cleaner. They are not any harder to debug, but do allow a debugger to potentially step over a large operation with an F10. I am very much about clean and neat code.

Lambda expressions are – lets admit it – cool. Smile But they serve a purpose, especially in threads to make “flow” more obvious. This is sort of the opposite end of the extension method argument in that sometimes putting a smaller operational block into the same method from which it will only ever be derived/called, just makes sense. It keeps it all in one place and easier to follow/debug.

Anyway, these are my opinions and different developers have their own styles. I invariably lose the conversation each time with my customers because by the time I get to their code: “it is just not the way we do things here.” Oh well. Punch
</rant>

With that said, lets get down to business. I have created a class called OfficeExtentions that works best in a separate Windows DLL project that you then reference in your core VSTO project. I demonstrate how to call it later. But here is the class:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;

namespace OfficeExtensions
{
public static class OfficeExtensions
{
public delegate void AfterPowerPointSaveHandler(PowerPoint.Presentation PobjPres);
public delegate void AfterWordSaveHandler(Word.Document PobjDoc);
public delegate void AfterExcelSaveHandler(Excel.Workbook PobjWb);
private const int MCintDELAY = 1000;

/// <summary>
/// POWERPOINT EXTENSION METHOD - AFTER SAVE
/// This function allows you to pass in a function that you want to
/// have called after PowerPoint has completed a SaveAs. If the user
/// only performs a save, your method will not be called. This only
/// gets called when the Save As Dialog is used.
/// </summary>
/// <param name="PobjApp"></param>
/// <param name="PobjFunc"></param>
public static void AttachToPowerPointAfterSaveAsEvent(this PowerPoint.Application PobjApp, AfterPowerPointSaveHandler PobjFunc)
{
// FIRST - we attach to the BeforeSave event
PobjApp.PresentationBeforeSave += (PowerPoint.Presentation PobjPres, ref bool PbolCancel) =>
{
// start a new thread using LAMBDA
new Thread(() =>
{
Thread.Sleep(MCintDELAY); // need this delay
if (hasSaveAsDialogOpen())
{
while (hasSaveAsDialogOpen())
{
Thread.Sleep(1);
System.Windows.Forms.Application.DoEvents();
}
// look for the saveAs dialog and as long as it
// is open we will wait right here
PobjFunc.Invoke(PobjPres);
}
}
).Start();
};
}

/// <summary>
/// WORD EXTENSION METHOD - AFTER SAVE
/// This function allows you to pass in a function that you want to
/// have called after Word has completed a SaveAs. If the user
/// only performs a save, your method will not be called. This only
/// gets called when the Save As Dialog is used.
/// </summary>
/// <param name="PobjApp"></param>
/// <param name="PobjFunc"></param>
public static void AttachToWordAfterSaveAsEvent(this Word.Application PobjApp, AfterWordSaveHandler PobjFunc)
{
// FIRST - we attach to the BeforeSave event
PobjApp.DocumentBeforeSave += (Word.Document PobjDoc, ref bool PbolSaveAsUi, ref bool PbolCancel) =>
{
// start a new thread using LAMBDA
new Thread(() =>
{
Thread.Sleep(MCintDELAY); // need this delay
if (hasSaveAsDialogOpen())
{
while (hasSaveAsDialogOpen())
{
Thread.Sleep(1);
System.Windows.Forms.Application.DoEvents();
}
// look for the saveAs dialog and as long as it
// is open we will wait right here
PobjFunc.Invoke(PobjDoc);
}
}
).Start();
};
}

/// <summary>
/// EXCEL EXTENSION METHOD - AFTER SAVE
/// This function allows you to pass in a function that you want to
/// have called after Excel has completed a SaveAs. If the user
/// only performs a save, your method will not be called. This only
/// gets called when the Save As Dialog is used.
/// </summary>
/// <param name="PobjApp"></param>
/// <param name="PobjFunc"></param>
public static void AttachToExcelAfterSaveAsEvent(this Excel.Application PobjApp, AfterExcelSaveHandler PobjFunc)
{
// FIRST - we attach to the BeforeSave event
PobjApp.WorkbookBeforeSave += (Excel.Workbook PobjWb, bool PbolSaveAsUi, ref bool PbolCancel) =>
{
// start a new thread using LAMBDA
new Thread(() =>
{
Thread.Sleep(MCintDELAY); // need this delay
if (hasSaveAsDialogOpen())
{
while (hasSaveAsDialogOpen())
{
Thread.Sleep(1);
System.Windows.Forms.Application.DoEvents();
}
// look for the saveAs dialog and as long as it
// is open we will wait right here
PobjFunc.Invoke(PobjWb);
}
}
).Start();
};
}

#region API CODE
[DllImport("user32.dll", SetLastError = true, CharSet = CharSet.Auto)]
private static extern IntPtr FindWindowEx(IntPtr parentHandle, IntPtr childAfter, string className, string windowTitle);

[DllImport("user32.dll", SetLastError = true)]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);

/// <summary>
/// Helper function to see if Word has any dialogs open
/// </summary>
/// <returns></returns>
private static bool hasSaveAsDialogOpen()
{
const string LCstrWIN_CLASS = "#32770";
const string LCstrWIN_CAPTION = "Save As";
IntPtr LintHWin = IntPtr.Zero;
LintHWin = FindWindowEx(IntPtr.Zero, LintHWin, LCstrWIN_CLASS, LCstrWIN_CAPTION);
uint PID = 0;
while (LintHWin != IntPtr.Zero)
{
// Make sure that the window handle that we got is for the current running
// Office Application process. We do this by checking if the PID for this window
// our Office application are the same.
GetWindowThreadProcessId(LintHWin, out PID);
if (PID == Process.GetCurrentProcess().Id)
break; // found it and it belongs to our app
// get next window
LintHWin = FindWindowEx(IntPtr.Zero, LintHWin, LCstrWIN_CLASS, LCstrWIN_CAPTION);
}
return LintHWin != IntPtr.Zero;
}
#endregion
}
}

What this is doing is giving an extension method off the root of Application that allows you to attach to the event. When the event occurs it calls your function parameter.

I have built three extensions methods:

  • AttachToExcelAfterSaveEvent
  • AttachToWordAfterSaveEvent
  • AttachToPowerPointAfterSaveEvent

NOTE: I tried to get creative and simply create one overloaded function called “AttachToAfterSaveAsEvent” but this failed to compile in the Excel and PowerPoint VSTO add-ins because they required the Word.Application to be defined. Fair enough – if I added Word.Application references to my Excel and PowerPoint VSTO project all was copasetic – but WHY. Seems there is some strangeness going on in the Interops when using overloaded functions. I did not have time to investigate this further, so rather than require you to reference all the apps in each of your separate application specific projects, I opted for different names. If you feel so inclined to get it to work… please let me know if you do and you figured it out.

Each of these methods works the same. They attach to the BeforeSave event in each application, and kick off a thread. The thread is used so we will know we are OUTSIDE of the event handler when they are executed. In the thread we issue a small delay to allow the dialog to appear, and then look for a Save As dialog. If we detect one we go into a loop looking for that Save As dialog to disappear. Once it does – we call the function you define/passed as a parameter.

Here are the different ways you can call it:

  • As a LAMBA expression:
/// <summary>
/// STARTUP
/// </summary>
/// <param name="PobjSender"></param>
/// <param name="PobjEventArgs"></param>
private void ThisAddIn_Startup(object PobjSender, System.EventArgs PobjEventArgs)
{
// Attach a method to the Extension methods After Before Save As event
// in this case we are doing LAMBDA expression
Application.AttachToPowerPointAfterSaveAsEvent((PowerPoint.Presentation PobjPres) =>
{
MessageBox.Show("The filename is: " + PobjPres.FullName);
});
}

  • Or traditional means:
/// <summary>
/// STARTUP
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
// Attach a method to the Extension methods After Before Save As event
Application.AttachToExcelAfterSaveAsEvent(HandleAfterBeforeSaveAs);
}

/// <summary>
/// Handles the after SaveAs dialog
/// </summary>
/// <param name="PobjPres"></param>
private void HandleAfterBeforeSaveAs(Excel.Workbook PobjWb)
{
MessageBox.Show("The filename is: " + PobjWb.FullName);
}

There it is.

Also, I recently added RATINGS to my posts. Please rate this post if you so feel inspired to. I would like to know it is being read and that you found it useful. Open-mouthed smile

How to determine if an Excel Workbook is Embedded… and more…

When working with a Visual Studio Tools for Office (VSTO) project, you may want to have your add-in behave differently when the Active Workbook is inside an embedding. More specifically, the user has inserted an Excel sheet into a Word Document, for example, and has just double-clicked on it and you find that you need to detect this scenario. The trick is to know when you are in an embedded scenario and as it turns out, this is not easy to do. If you are an OLE guru, you might check the Container property on the Workbook Object:

However, this property always throws an Exception for Office Document types. It specifically calls out Internet Explorer, for a reason. This property does not work with OLE Embeddings in other Office documents. You can check the path length and as I have seen in many solutions hardcoding for “Workbook in…” but this fails for multi-language solutions.

What I found you have to do is access a series of OLE properties through C# to get the information you need. And as an added bonus, not only can you determine if Excel is embedded, but you can also get the Name of the class object it is embedded inside of and the Moniker / Filename of the file it is embedded in. I created the following Extension methods for Excel:

/// <summary>
/// EXTENSION METHOD CLASS FOR EXCEL
/// </summary>
public static class ExcelExtensionMethods
{
    [DllImport("ole32.dll")]
    static extern int CreateBindCtx(uint reserved, out IBindCtx ppbc);

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// Checks to see if the Workbook is embeeded inside of 
    /// another ActiveX Document type, sy=uch as Word or Excel.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static bool IsEmbedded(this Excel.Workbook PobjWb)
    {
        if (PobjWb.Path == null || PobjWb.Path.Length == 0)
        {
            try
            {
                // requires using Microsoft.VisualStudio.OLE.Interop;
                // and you have to manually add this to reference from here:
                // C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.VisualStudio.OLE.Interop.dll
                IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
                IOleClientSite LobjPpClientSite;
                // get the client site
                LobjOleObject.GetClientSite(out LobjPpClientSite);
                // if there is one - we are embedded
                if (LobjPpClientSite != null)
                {
                    return true;
                }
                else
                {
                    // not embedded
                    return false;
                }
            }
            catch (Exception ex)
            {
                // exception
                Debug.Print(ex.ToString());
                return false;
            }
            finally { }
        }
        else
        {
            // not embedded
            return false;
        }
    }

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// This method return the name of the class that we
    /// are embedded inside of.
    /// If we are not embedded it return null.
    /// If there is any exception it return null.
    /// If the container cannot be accessed it returns UNKNOWN.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static string EmbedClassName(this Excel.Workbook PobjWb)
    {
        try
        {
            IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
            IOleClientSite LobjPpClientSite;
            // get the client site
            LobjOleObject.GetClientSite(out LobjPpClientSite);
            if (LobjPpClientSite != null)
            {
                IOleContainer LobjPpContainer;
                LobjPpClientSite.GetContainer(out LobjPpContainer);
                if (LobjPpContainer != null)
                {
                    return LobjPpContainer.GetType().Name;
                }
                else
                {
                    // something wrong - container is not valid
                    return "UNKNOWN";
                }
            }
            else
            {
                // not embedded
                return null;
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.ToString());
            return null; // failed
        }
    }

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// Get the full path to the file that the workbook is embedded 
    /// inside of. 
    /// If we are not embeeded then this will return null.
    /// If we are embedded but there are issues with the container
    /// or an exception occurs, it will return null.
    /// Otherwise we get the full path and filename.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static string EmbedMoniker(this Excel.Workbook PobjWb)
    {
        try
        {
            IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
            IOleClientSite LobjPpClientSite;
            // get the client site
            LobjOleObject.GetClientSite(out LobjPpClientSite);
            if (LobjPpClientSite != null)
            {
                IOleContainer LobjPpContainer;
                LobjPpClientSite.GetContainer(out LobjPpContainer);
                if (LobjPpContainer != null)
                {
                    // get the moniker
                    IMoniker LobjMoniker;
                    LobjPpClientSite.GetMoniker((uint)OLEGETMONIKER.OLEGETMONIKER_FORCEASSIGN,
                                                (uint)OLEWHICHMK.OLEWHICHMK_OBJFULL,
                                                out LobjMoniker);
                    if (LobjMoniker != null)
                    {
                        // now pull the moniker display name
                        // this will be in the form of PATH!Context
                        string LstrDisplayName;
                        IBindCtx LobjCtx = null;
                        CreateBindCtx(0, out LobjCtx); // required (imported function)
                        LobjMoniker.GetDisplayName(LobjCtx, null, out LstrDisplayName);
                        // remove context is exists
                        if (LstrDisplayName.Contains("!"))
                        {
                            string[] LobjMonikerArray = LstrDisplayName.Split('!');
                            // return the first part - which should be the path
                            return LobjMonikerArray[0];
                        }
                        else
                        {
                            // return full display name
                            return LstrDisplayName;
                        }
                    }
                    else
                    {
                        // no moniker value
                        return null;
                    }
                }
                else
                {
                    // something wrong - container is not valid
                    return null;
                }
            }
            else
            {
                // not embedded
                return null;
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.ToString());
            return null; // failed
        }
    }
}

Pay close attention to the comments, because you will not find the OLE reference you need in the References window. You will have to browse to the path given and manually select it.

Now, to use it, I hook to the WorkbookActivate event, like this:

/// <summary>
/// STARTUP
/// </summary>
/// <param name="PobjSender"></param>
/// <param name="pObjEventArgs"></param>
private void ThisAddIn_Startup(object PobjSender, System.EventArgs pObjEventArgs)
{
    Application.WorkbookActivate += new Excel.AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);
}

/// <summary>
/// Workbook Activate event fires when any workbook is activated.
/// </summary>
/// <param name="PobjWb"></param>
void Application_WorkbookActivate(Excel.Workbook PobjWb)
{
    // call the extension method created below
    if (PobjWb.IsEmbedded())
    {
        string LstrClass = PobjWb.EmbedClassName();
        string LstrPath = PobjWb.EmbedMoniker();
        MessageBox.Show("This workbook is embedded in a [" + LstrClass + "] " +
                        "with a path of: \n\n\t" + LstrPath);
    }
}

What you can see from the code above is that on WorkbookActivate, I check to see if the workbook is embedded and if it is, then I get the Class Name by calling my other Extension Method – EmbedClassName() – and then I get the path to the file it is embedded inside of (for example, the Word document path that contains the embedded Excel sheet) by calling yet another Extension Method – EmbedMoniker().

Detect When an Excel Chart is Deleted

A customer of mine (thanks Simon! Hot smile) contacted me with a solution he discovered while trying to determine if a user deleted a chart from a workbook. The following code belongs in a Ribbon.cs with a button to insert the chart.

When the user clicks the button to add the chart, the deactivate event is then attached to the chart. The trick is to throw an exception to detect the deletion. When the chart is deleted, the deactivate event will fire, but any attempt to reference any property of the chart will fail with an exception.In this case an attempt is made to access the “.Name” property of the chart. If it is deleted, it will throw an exception and tell you the chart was deleted. Here is the code:

Excel.Chart chart;
private void button1_Click(object sender,
                           RibbonControlEventArgs e)
{
    try
    {
        // hook the deactivate event
        chart = Globals.ThisAddIn.Application.ActiveChart;
        if (chart != null)
        {
            chart.Deactivate += new
                Excel.ChartEvents_DeactivateEventHandler(
                        chart_Deactivate);
        }
    }
    catch (Exception ex)
    { }
}

/// <summary>
/// Caused when a selected chart is deactivated.
/// </summary>
void chart_Deactivate()
{
    try
    {
        string a = chart.Name;
        MessageBox.Show("Chart has been deselected " +
                        " but it is still around");

    }
    catch
    {
        MessageBox.Show("Chart has been deleted!");
        return;
    }
}