Detecting Print in Outlook (VSTO/C#)

This is a common problem in Outlook. You might have tried to override the Ribbon settings for Print in Outlook to find that your code never gets run when the user clicks Print.

There is also not any events in the Outlook object model to detect Print either. So if you need to detect the user pressing the print button, you are out of luck.

While it is still not possible to detect the print button being pressed, you can at least detect when the user has selected the Print tab on the backstage.

The following code uses a background thread and a series of Windows API calls to FindWindow/FindWindowEx to detect when the Print tab on the backstage is opened:

[DllImport("user32.dll")]
public static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
[DllImport("user32.dll", SetLastError = true)]
public static extern IntPtr FindWindowEx(IntPtr parentHandle, IntPtr childAfter, string className, string windowTitle);
/// <summary>
/// Startup for Outlook Add-in
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
// we start by creating a background thread and look for a specific
// set of windows to appear, then we know the user clicked print
new Thread(() =>
{
while (true)
{
Thread.Sleep(1000);
CheckForPrint();
}
}).Start();
}
/// <summary>
/// Checks to see if the user has opened backstage and
/// selected the Print tab
/// </summary>
private void CheckForPrint()
{
try
{
// depending on whether we have an inspector active, or the explorer
// active we will need to get the caption to FindWindow
string LstrCaption = "";
if(Application.ActiveWindow() is Outlook.Inspector)
{
// Active inspector caption
LstrCaption = ((Outlook.Inspector)Application.ActiveWindow()).Caption;
}
else if(Application.ActiveWindow() is Outlook.Explorer)
{
// Active explorer caption
LstrCaption = ((Outlook.Explorer)Application.ActiveWindow()).Caption;
}
// get the window handle
IntPtr LintHostHandle = FindWindow(null, LstrCaption);
if (LintHostHandle == IntPtr.Zero) return; // if we cannot find it – nevermind
// create a list of windows to find (in reverse order)
// 4) rctrl_renwnd32 – is the print preview window
// 3) NetUICtrlNotifySink – is whole Print options and preview
// 2) NetUIHWND – is the the entire print tab
// 1) FullpageUIHost – is the backstage page
Stack<string> LobjWindowClasses = new Stack<string> (
new string[] { "rctrl_renwnd32", "NetUICtrlNotifySink", "NetUIHWND", "FullpageUIHost" });
// recursive call back to find each window in the stack.
// if all of them are found, then present a message to the user
if(FindWindowStack(LintHostHandle, LobjWindowClasses))
{
MessageBox.Show("You have clicked on the Print Tab in Outlook.");
}
}
catch { }
}
/// <summary>
/// RECURSIVE
/// This function will take the window classnames in the provided stack
/// and then find each one in order via recursive calls. If all of them
/// are found – we return true = found
/// </summary>
/// <param name="PintHandle"></param>
/// <param name="PobjStack"></param>
/// <returns></returns>
private bool FindWindowStack(IntPtr PintHandle, Stack<string> PobjStack)
{
try
{
// get the window with the classname being popped off the stack
IntPtr LintNewHandle = FindWindowEx(PintHandle, IntPtr.Zero, PobjStack.Pop(), "");
if(LintNewHandle != IntPtr.Zero && PobjStack.Count == 0)
{
return true; // found it
}
else if(LintNewHandle!= IntPtr.Zero)
{
// found a window, but the stack still has items, call next one
return FindWindowStack(LintNewHandle, PobjStack);
}
else
{
// did not find it
return false;
}
}
catch
{
// oops
return false;
}
}

An Asynchronous MessageBox

Recently, I had a project I was working on where we needed to be able to notify the user of a possible problem during a long running process. The results would not be ruined, or wrong, just likely incomplete. In this particular case we were collecting several pieces of information and sometimes one of those pieces were not available (say, for example the server was down, the file was missing, or it took too long). We wanted to let the user know the process had encountered an issue, but not stop it. Yet we wanted to also give them the option to retry it. So we presented an Abort, Retry, Cancel dialog like this one:

messagebox

The problem is that with a traditional MessageBox the entire process would be frozen. If this happens immediately after the user heads off to lunch, when they get back there would be no report, no data and most of the process would not have been run yet, for example.

What I needed was for the process to continue, but to allow the user to be able to take action before the process completed on anything that was found. For example, they see this error above and think: “oh crap, I forgot to copy over the report file.” So they put the correct file in place and hit “Retry.”

Therefore, I created the AsyncMessageBox class:


using System;
using System.Runtime.InteropServices;
using System.Threading;
using System.Windows.Forms;
namespace NonModalMessageBox
{
/// <summary>
/// Provides an asyncronous MessageBox. You can use this static class to
/// present a message to the usser while your code can continue to run.
/// You can attach to the AsynMessagebox.MessageboxClosed event to get the
/// result from the dialog once the user does close it.
/// </summary>
public static class AsyncMessageBox
{
private static readonly IntPtr HWND_TOPMOST = new IntPtr(1);
private static readonly uint SWP_NOMOVE = 0x0002;
private static readonly uint SWP_NOSIZE = 0x0001;
private static readonly uint SWP_SHOWWINDOW = 0x0040;
[DllImport("user32")]
private static extern IntPtr FindWindow(string PstrClassName, string PstrCaption);
[DllImport("user32")]
private static extern void SetWindowPos(IntPtr PintWnd, IntPtr PintWndInsertAfter, int PintX, int PintY, int PintCx, int PintCy, uint uFlags);
public delegate void MessageBoxClosedHandler(object PobjSender, MessageBoxClosedEventArgs PobjEventArgs);
public static event MessageBoxClosedHandler MessageBoxClosed;
private static bool MbolAlreadyShowing = false;
/// <summary>
/// Shows an asyncronous dialog
/// Fires the MessageBoxClosed event when it is closed.
/// This is a static messagebox, so only one can be displayed at a time
/// Once called, the event handler is detached
/// </summary>
/// <param name="PstrText">The message in the message box</param>
/// <param name="PstrCaption">The cpation on the message box</param>
/// <param name="PobjButtons">The buttons for the message box</param>
/// <param name="PobjIcon">The icon for the messafe box</param>
/// <param name="PobjDefault">The default button selected in the messagebox</param>
/// <returns></returns>
public static bool Show(string PstrText, string PstrCaption = "", MessageBoxButtons PobjButtons = MessageBoxButtons.OK, MessageBoxIcon PobjIcon = MessageBoxIcon.None, MessageBoxDefaultButton PobjDefault = MessageBoxDefaultButton.Button1)
{
try
{
if (MbolAlreadyShowing) return false; // failed – already displayed
DialogResult LobjResult = DialogResult.None;
// start a thread to show the dialog
new Thread(() => {
MbolAlreadyShowing = true;
LobjResult = MessageBox.Show(PstrText, PstrCaption, PobjButtons, PobjIcon, PobjDefault);
MbolAlreadyShowing = false;
}).Start();
// start a separate thread to wait for the result from above
new Thread(() => {
// now make it topmost
IntPtr LintHwnd = FindWindow("#32770", PstrCaption);
SetWindowPos(LintHwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOSIZE | SWP_NOMOVE | SWP_SHOWWINDOW);
// Stay here until we get a result
while (LobjResult == DialogResult.None)
{
Thread.Sleep(10);
Application.DoEvents();
}
// create a hidden form so we can invoke back to the UI thread
// otherwise anyone attached to the event handler will get an
// exception if they try anything with the UI
using (Form LobjForm = new Form { ShowInTaskbar = false, Opacity = 0 })
{
LobjForm.Show(); // show hidden form – ui thread
// fire the event
LobjForm.Invoke(new Action(() => {
MessageBoxClosed?.Invoke(new object(), new MessageBoxClosedEventArgs(LobjResult));
MessageBoxClosed = null; // important to release this
}));
LobjForm.Close(); // close hidden form
}
}).Start();
return true; // created
}
catch
{
return false; // failed
}
}
}
/// <summary>
/// Event arguments for an AsyncMessageBox
/// </summary>
public class MessageBoxClosedEventArgs
{
public DialogResult Result { get; private set; }
public MessageBoxClosedEventArgs(DialogResult PobjResult)
{
Result = PobjResult;
}
}
}

Under the covers this uses a traditional MessageBox, but on background thread. You need to attach to the MessageBoxClosed event before you make the AsyncMessageBox.Show() call if you want to get the result. Here is how you use this method:


AsyncMessageBox.MessageBoxClosed += (o, e) =>
{
// handle the user response here
if(e.Result == DialogResult.Retry)
{
// retry code here
}
};
AsyncMessageBox.Show("There was an issue accessing 'quaterly report.xlsx'.",
"Quarterly Report Generator",
MessageBoxButtons.AbortRetryIgnore,
MessageBoxIcon.Exclamation);

Anyway, thought this might be useful, so I have shared it. wlEmoticon-hotsmile.png

Extended MessageBox Library

Several years ago I created this class to assist with making a better, more useful Message Box windows in Add-ins and other projects. You can find it here in my GitHub repository:

Extended MessageBox Library

There were a few times I wanted to show the user a message, but also provide them with a link. Other times I found that I needed to present an error to the user, but wanted to hide more “technical” details from them, unless they clicked a More Info button. I also found cases where I needed to present an error if the user wanted to stop a potentially unattended process, but also wanted the message to dismiss after a period of time so the process could continue. Other times I wanted to present the user with a useful message, but an option to “Do not show this message again.”

After creating custom forms, over and over again, this C# library was born. I recently was writing a VSTO add-in that needed the later option – do not show again – and dusted off this old library, cleaned it up a bit (not not completely) and used it again. I figured, for posterity, I would share this with everyone since I have found it so useful over the years.

For the most part you use this exactly like you would the regular MessageBox object, with some extra goodies included.

For example, this will create a very simple Message box that will look fmailiar:

ExtendedMessageBox.Show(“Hello World”);

Produces this:

simple_msgbox

A more common scenario you might need with a checkbox, looks like this:


ExtendedDialogResult LobjResult = ExtendedMessageBox.Show("Hello World – Are you ok?",
"The Office Context",
MessageBoxButtons.OKCancel,
MessageBoxIcon.Information,
"Do not show this message again.");
if(LobjResult.Result == DialogResult.OK && LobjResult.IsChecked)
{
// do something
}

checkbox_msgbox

There are a lot more options and capabilities. I will not cover them all here, but they will be detailed in the README in the GitHub repository.

Please let me know if you have any questions or issues.

Docs.com is Retiring

I went all out using Docs.com for my code starting last year. But, I just got notified that it is being retired this December. wlEmoticon-cryingface.png

However, effectively immediately, all the code pages I was using now display an error like this all over my site:

docs.PNG

This is VERY frustrating I know. wlEmoticon-sadsmile.png

I have decided I will be converting all my code blocks over to Gist. Unless anyone has a better idea for a WordPress site. P.S. I really do NOT want to use add-on’s either. Anyway, this may take me a while as I have a LOT of code content out there on Docs.com. So, please bear with me.

Also, please note, I (kind of) anticipated this. In all my code pages there is a link above every Docs.com embedding you can click to expand and to see the full code on my site:

link.PNG

Using Outlook.Items.Restrict()

Recently, I was working on an Outlook Visual Studio Tools for Office (VSTO) solution and encountered a problem with unexpected results with the Restrict() method. What I was doing was trying to locate all the appointments (meeting and recurrence) that occurred today. The Restrict command was returning items where the Start and End date properties that were from 2000, 2001, 2010, etc. I quickly deduced that it was also returning recurring items that were dated back to the first occurrence. Here is what my code looked like:

[code lang=”csharp” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
///
<summary>
/// EXTENSION METHOD
/// Get all the appointments between the given date times
/// </summary>

/// <param name="PobjItems"></param>
/// <param name="PobjStart"></param>
/// <param name="PobjEnd"></param>
/// <returns></returns>
public static List<Outlook.AppointmentItem> FindAppointments(this Outlook.Items PobjItems,
DateTime PobjStart,
DateTime PobjEnd)
{
try
{
List<Outlook.AppointmentItem> LobjReturn = new List<Outlook.AppointmentItem>();
string LstrCriteria = "[Start] >= \"" + PobjStart.ToString("g") + "\" AND [End] <= \"" + PobjEnd.ToString("g") + "\"";
// we want to find the oldest first. This way we do
// not get stuck on all day appointments that are conflicting
// with the current value.
PobjItems.Sort("[End]");
PobjItems.IncludeRecurrences = true;
PobjItems = PobjItems.Restrict(LstrCriteria);
object LobjItem = PobjItems.GetFirst();
// chcek the appointments or meetings against the
// the rules and set Skype status
Outlook.AppointmentItem LobjAppt = null;
do
{
if (LobjItem is Outlook.MeetingItem)
{
LobjAppt = ((Outlook.MeetingItem)LobjItem).GetAssociatedAppointment(false);
}
else
{
LobjAppt = (Outlook.AppointmentItem)LobjItem;
}
// as long as we are not null
if (LobjItem != null)
{
LobjReturn.Add(LobjAppt);
LobjItem = PobjItems.GetNext();
}
}
while (LobjItem != null);
// done
return LobjReturn;
}
catch (Exception PobjEx)
{
PobjEx.Log(false);
return null;
}
}
[/code]


/// <summary>
/// EXTENSION METHOD
/// Get all the appointments between the given date times
/// </summary>
/// <param name="PobjItems"></param>
/// <param name="PobjStart"></param>
/// <param name="PobjEnd"></param>
/// <returns></returns>
public static List<Outlook.AppointmentItem> FindAppointments(this Outlook.Items PobjItems,
DateTime PobjStart,
DateTime PobjEnd)
{
try
{
List<Outlook.AppointmentItem> LobjReturn = new List<Outlook.AppointmentItem>();
string LstrCriteria = "[Start] >= \"" + PobjStart.ToString("g") + "\" AND [End] <= \"" + PobjEnd.ToString("g") + "\"";
// we want to find the oldest first. This way we do
// not get stuck on all day appointments that are conflicting
// with the current value.
PobjItems.Sort("[End]");
PobjItems.IncludeRecurrences = true;
PobjItems = PobjItems.Restrict(LstrCriteria);
object LobjItem = PobjItems.GetFirst();
// chcek the appointments or meetings against the
// the rules and set Skype status
Outlook.AppointmentItem LobjAppt = null;
do
{
if (LobjItem is Outlook.MeetingItem)
{
LobjAppt = ((Outlook.MeetingItem)LobjItem).GetAssociatedAppointment(false);
}
else
{
LobjAppt = (Outlook.AppointmentItem)LobjItem;
}
// as long as we are not null
if (LobjItem != null)
{
LobjReturn.Add(LobjAppt);
LobjItem = PobjItems.GetNext();
}
}
while (LobjItem != null);
// done
return LobjReturn;
}
catch (Exception PobjEx)
{
PobjEx.Log(false);
return null;
}
}

view raw

broken.cs

hosted with ❤ by GitHub

What I created is an extension item off the Outlook.Items object that allows me to easily restrict them to appointments found between two given dates. This is actually a very useful extension method. If it worked the way I expected it to. What I found out is that in order to get recurring items that occur today which have todays date in the Start and End date properties, you MUST sort the results by the [Start] date. You cannot sort by the [End] date as I had done. Almost exactly the same, here is the fixed function:

[code lang=”csharp” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
///
<summary>
/// EXTENSION METHOD
/// Get all the appointments between the given date times
/// </summary>

/// <param name="PobjItems"></param>
/// <param name="PobjStart"></param>
/// <param name="PobjEnd"></param>
/// <returns></returns>
public static List<Outlook.AppointmentItem> FindAppointments(this Outlook.Items PobjItems,
DateTime PobjStart,
DateTime PobjEnd)
{
try
{
List<Outlook.AppointmentItem> LobjReturn = new List<Outlook.AppointmentItem>();
string LstrCriteria = "[Start] >= \"" + PobjStart.ToString("g") + "\" AND [End] <= \"" + PobjEnd.ToString("g") + "\"";
// FIX: we must use [Start] in the Sort to get the right recurrence
// for todays date…
PobjItems.Sort("[Start]");
PobjItems.IncludeRecurrences = true;
PobjItems = PobjItems.Restrict(LstrCriteria);
object LobjItem = PobjItems.GetFirst();
// chcek the appointments or meetings against the
// the rules and set Skype status
Outlook.AppointmentItem LobjAppt = null;
do
{
if (LobjItem is Outlook.MeetingItem)
{
LobjAppt = ((Outlook.MeetingItem)LobjItem).GetAssociatedAppointment(false);
}
else
{
LobjAppt = (Outlook.AppointmentItem)LobjItem;
}
// as long as we are not null
if (LobjItem != null)
{
LobjReturn.Add(LobjAppt);
LobjItem = PobjItems.GetNext();
}
}
while (LobjItem != null);
// done
return LobjReturn;
}
catch (Exception PobjEx)
{
PobjEx.Log(false);
return null;
}
}
[/code]


/// <summary>
/// EXTENSION METHOD
/// Get all the appointments between the given date times
/// </summary>
/// <param name="PobjItems"></param>
/// <param name="PobjStart"></param>
/// <param name="PobjEnd"></param>
/// <returns></returns>
public static List<Outlook.AppointmentItem> FindAppointments(this Outlook.Items PobjItems,
DateTime PobjStart,
DateTime PobjEnd)
{
try
{
List<Outlook.AppointmentItem> LobjReturn = new List<Outlook.AppointmentItem>();
string LstrCriteria = "[Start] >= \"" + PobjStart.ToString("g") + "\" AND [End] <= \"" + PobjEnd.ToString("g") + "\"";
// FIX: we must use [Start] in the Sort to get the right recurrence
// for todays date…
PobjItems.Sort("[Start]");
PobjItems.IncludeRecurrences = true;
PobjItems = PobjItems.Restrict(LstrCriteria);
object LobjItem = PobjItems.GetFirst();
// chcek the appointments or meetings against the
// the rules and set Skype status
Outlook.AppointmentItem LobjAppt = null;
do
{
if (LobjItem is Outlook.MeetingItem)
{
LobjAppt = ((Outlook.MeetingItem)LobjItem).GetAssociatedAppointment(false);
}
else
{
LobjAppt = (Outlook.AppointmentItem)LobjItem;
Debug.Print(" >>>>>>>>>>> " + LobjAppt.Subject);
}
// as long as we are not null
if (LobjItem != null)
{
LobjReturn.Add(LobjAppt);
LobjItem = PobjItems.GetNext();
}
}
while (LobjItem != null);
// done
return LobjReturn;
}
catch (Exception PobjEx)
{
PobjEx.Log(false);
return null;
}
}

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().

VSTO and COM/OLE…

If you have been doing much work in VSTO and especially around Excel and embeddings, you may have been bit by this bug. Does this error look familiar:

clip_image002

“The program used to create this object is Excel. That program is either not installed on your computer or it is not responding. To edit this object, install Excel or ensure that any dialog boxes in Excel are closed.”

This error can be caused by the following:

  • A .NET3.5 Add-in or a .NET 4.0 Add-in
  • You have attached to the WorkbookOpen and/or WorkbookActivate events.
  • You are trying to edit/open/double-click on an embedded Excel instance inside a Word or PowerPoint document.

There have been several reports of this problem on the Microsoft MSDN site:

There are also a number of KB articles that document the problem and attribute it to specific programs:

The problem is specifically documented here:

Simple Solution

The basic simple answer is to place a Marshal.ComReleaseObject(Wb) at the end (or better, in the Finally block) of your event handlers. This will properly allow Word and excel to handle the OLE communication by not having VSTO hang on to an instance handle of the workbook, therefore causing the error.

And this is not carte blanche to start placing ComReleaseObject() all over your code. I have found VERY VERY few limited cases where using ComReleaseObject() in an add-in necessary. And this is one of them. Hot smile

Orphan Issue

It is not a panacea, either. Confused smile While it resolves the issues of OLE initialization and allows you to edit your Excel embedding in Word (as one example), it does not prevent one other scenario that I like to call “Orphaned Excel.” In this scenario, you edit your embedded Excel instance in another Window (usually via a right-click / Ole Object / Open). If you leave Excel open, return to Word and close the document, Excel should close. But in the VSTO COM/OLE scenario it may not – it will remain open with the embedded workbook still editable. However, it is orphaned an no longer associated with its container. Any edits will be lost.

That is where a solution I created for Excel/Word OLE interaction comes in here:

This add-in is very well commented and explains the following:

  • When a workbook is opened, it looks to see if it is embedded.
  • If it is, it connects to the running instance of Word, and gets a reference to the parent document.
  • A timer in the add-in will then continually check the status of the document
  • If the parent document is still opened, nothing happens. However, if Word is existed or the parent document is closed, the child embedding is forced closed.

IMPORTANT NOTE

However, and this is important note. For everything you do, you are in the sandbox with other kids. Sad smile Your add-in is loading in the same AppDomain as everybody else. If there is another VSTO 3.5/4.0 Add-in loaded in Excel and that add-in is not doing any of the above – well, your still going to have problems.

That is what makes this issue so vexing. Steaming mad You can play by all the rules, but you cannot prevent other kids from throwing sand. This is why I see some customers going to the extreme to manage out (disable) all other COM add-ins when they load their solution. But this does not work for HKEY_LOCAL_MACHINE Loaded add-ins.

There are few options and most involve a lot of code. I say, TEST. If your are in an Enterprise environment, test all your VSTO add-ins together, find code owners and get everyone on the same page. If it is a vendor add-in causing the problem, point them here. Smile

Creating a Loader Add-in (Master Add-in)

I have now helped about half a dozen customers over the last 3 years to perform this very same task. In each case the scenario is the same:

  • They have documents that are very specific to their system
  • They want to create an add-in to assist with the management of these documents.
  • They only want the add-in to load when one of their document is opened.
  • BONUS: They also want to remove other add-ins
  • BONUS: They would like a pristine instance of Word or Excel.

There are several methods to accomplish each of these tasks. But the one I find is easiest to maintain is what I call the “Master Add-in” approach.

In the “Master Add-in” you essentially attach one event: DocumentOpen or WorkbookOpen. You place code in the event to detect whether it is a document you care about. This can be done in several ways:

  • You can look at the path where the file came from.
  • You can look at the name of the file, if there is a specific naming convention you follow.
  • Or, you can tag he document with Document Properties or Custom Xml Parts.

Once you have identified that it is a document you care about you follow this basic process:

  • Get the path to the file being open and store it.
  • Close the workbook or document being passed into the Open event.
  • Create a new Instance of Word or Excel.
  • Iterate through the COM Add-ins collection and the Add-ins collection and disable everything you do not want running. This includes disabling the Master Add-in as you do not want it running for the next parts.
  • Locate your COM Add-in in the collection and set Connect = true.
  • Open the document or workbook from the application object.

You will now have two instances of Excel or Word open. One that the user was originally working with which has the Master Add-in loaded and then the new one that is customized with your specific add-in and only your add-in.

This is useful for when you have multiple versions of your system and you can update the Master to recognize which version to launch.

Getting this to work just right is sometimes a challenge and you have to be careful not to disable all the add-ins for all instances. I have a few customer examples that I have built and will work on cleaning them up. I will combine the best parts and will write a new entry in the future which will walk you through creating one.