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