IDTExtensibility2 - Addin, catching the Excel Application

I was revising the topic of writing a VBA addin using the IDTExtensibility2 interface and it struck me that the Excel.Application object is not passed in. Instead, in the argument list for OnConnect the first argument is of type VBE. This makes sense because the addin is likely to add menus to the menu bars of the development environment. However, I think it would have been nice to also pass the Excel.Application object because the addin may well want to add workbooks etc.

I set about remedying the this and hits some problems. I solved this problems along the and I am happy to share working code below.

So acquiring the running instance of Excel turned out to be quite challenging. I had hoped using the System.Diagnostics.Process class would give me easy access but it didn't; even the MainWindowHandle returned the VBE's development window instead Excel's main window. Eventually, I had to grab the process id and then look for Excel windows registered as children of the desktop window and match the owning process id. Once in possession of an XLMAIN handle I grab the XLDESK child handle and then in turn its EXCEL7 child handle. Finally, we can pass the EXCEL7 handle to the AccessibleObjectFromWindow() to acquire an IDispatch interface to an Excel workbook's window and then we navigate to the Excel.Application object. All this extra code is housed in the Accessibility class. N.B. this method is dependent of at least one workbook being present (even if it is a .XLAM hidden workbook).

The C# source code follows, this is a C# Assembly DLL project. It does have some extra registration entries, see underneath.

using System;
using System.Runtime.InteropServices;
using System.ComponentModel;
using Xl = Microsoft.Office.Interop.Excel;  // Add reference to Microsoft Excel type library
using System.Diagnostics;
using Extensibility; // Add reference to 'C:\Program Files (x86)\Common Files\microsoft shared\MSEnv\PublicAssemblies\extensibility.dll'
using Microsoft.Vbe.Interop;

/* In Project Properties Build tab, check the checkbox 'Register for COM Interop' */
/* In Project Properties Debug tab, edit 'Start external program' to C:\Program Files\Microsoft Office 15\root\office15\excel.exe */

namespace VBAIDEAddin
{

    [
        ComVisible(true),
        Guid("1D5F24D3-E24C-4283-AAA9-AAED51B146F7"), 
        ProgId("VBAIDEAddin.Connect"),
        ClassInterface(ClassInterfaceType.None),
        ComDefaultInterface(typeof(IDTExtensibility2)),
        EditorBrowsable(EditorBrowsableState.Never)
    ]
    public class Class1 : IDTExtensibility2
    {
        private VBE _vbe;
        Xl.Application xlApp = null;
        private AddIn _AddIn;

        public void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
        {
            try
            {
                _vbe = (VBE)Application; // cast to strongly typed
                _AddIn = (AddIn)AddInInst;

                if (Accessibility.GetCurrentProcess(ref xlApp))
                {
                    xlApp.StatusBar = "addin was here";
                }

                switch (ConnectMode)
                {
                    case Extensibility.ext_ConnectMode.ext_cm_Startup:
                        break;
                    case Extensibility.ext_ConnectMode.ext_cm_AfterStartup:
                        InitializeAddIn();

                        break;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

        public void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom)
        {
            switch (RemoveMode)
            {
                case ext_DisconnectMode.ext_dm_UserClosed:
                    ShutdownAddIn();
                    break;

                case ext_DisconnectMode.ext_dm_HostShutdown:
                    // some hosts do not call OnBeginShutdown: this mitigates it.
                    ShutdownAddIn();
                    break;
            }
        }

        public void OnAddInsUpdate(ref Array custom)
        { /*throw new NotImplementedException(); */  }

        public void OnStartupComplete(ref Array custom)
        { /*throw new NotImplementedException(); */  }

        public void OnBeginShutdown(ref Array custom)
        { /*throw new NotImplementedException(); */  }

        private void ShutdownAddIn()
        {  }

        private void InitializeAddIn()
        {  }
    }


    public class Accessibility
    {
        [DllImport("user32.dll", SetLastError = true)]
        static extern IntPtr FindWindowEx(IntPtr hwndParent, IntPtr hwndChildAfter, string lpszClass, string lpszWindow);

        [DllImport("oleacc.dll", SetLastError = true)]
        internal static extern int AccessibleObjectFromWindow(IntPtr hwnd, uint id, ref Guid iid,
                                                    [In, Out, MarshalAs(UnmanagedType.IUnknown)] ref object ppvObject);

        [DllImport("kernel32.dll")]
        static extern uint GetCurrentProcessId();

        [DllImport("user32.dll")]
        static extern uint GetWindowThreadProcessId(IntPtr hWnd, out int processId);

        public static bool GetCurrentProcess(ref Xl.Application appRetVal)
        {
            bool bRetVal = false;
            appRetVal = null;
            Process currentProcess = Process.GetCurrentProcess();
            if (currentProcess.MainModule.FileName.EndsWith("excel.exe", StringComparison.CurrentCultureIgnoreCase))
            {
                bRetVal = GetXlMainByProcessId(currentProcess.Id, ref appRetVal);
            }
            return bRetVal;
        }

        static bool GetXlMainByProcessId(int processId, ref Xl.Application appRetVal)
        {
            bool bRetVal = false;
            appRetVal = null;

            IntPtr hWndXlMain = FindWindowEx(IntPtr.Zero, IntPtr.Zero, "XLMAIN", null);
            while (hWndXlMain != IntPtr.Zero)
            {
                int hOwnerProcess = 0;
                GetWindowThreadProcessId(hWndXlMain, out hOwnerProcess);
                if (processId == (int)hOwnerProcess)
                {
                    IntPtr lHwndDesk = FindWindowEx(hWndXlMain, IntPtr.Zero, "XLDESK", "");
                    if (lHwndDesk != IntPtr.Zero)
                    {
                        IntPtr lHwndExcel7 = FindWindowEx(lHwndDesk, IntPtr.Zero, "EXCEL7", null);
                        if (lHwndExcel7 != IntPtr.Zero)
                        {
                            Guid IID_IDispatch = new Guid("{00020400-0000-0000-C000-000000000046}");
                            const uint OBJID_NATIVEOM = 0xFFFFFFF0;
                            object app = null;
                            if (AccessibleObjectFromWindow(lHwndExcel7, OBJID_NATIVEOM, ref IID_IDispatch, ref app) == 0)
                            {
                                dynamic appWindow = app;
                                appRetVal = appWindow.Application;
                                return true;
                            }
                        }
                    }
                }
                hWndXlMain = FindWindowEx(IntPtr.Zero, hWndXlMain, "XLMAIN", null);
            }
            return bRetVal;
        }
    }
}

Here are the extra registry entries. Copy it to notepad and save off as a .reg file then double click on it to write entries to registry. If you are using this code for your own addin then please change the GUIDs (as they are meant to be unique).

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE\6.0\Addins\VBAIDEAddin.Connect]
"CommandLineSafe"=dword:00000000
"Description"="Cut down version"
"LoadBehavior"=dword:00000000
"FriendlyName"="VBAIDEAddin"


[HKEY_CLASSES_ROOT\CLSID\1D5F24D3-E24C-4283-AAA9-AAED51B146F7]
@="VBAIDEAddin.Connect"

[HKEY_CLASSES_ROOT\CLSID\1D5F24D3-E24C-4283-AAA9-AAED51B146F7\Implemented Categories]

[HKEY_CLASSES_ROOT\CLSID\1D5F24D3-E24C-4283-AAA9-AAED51B146F7\InprocServer32]
@="mscoree.dll"
"ThreadingModel"="Both"
"Class"="VBAIDEAddin.Connect"
"Assembly"="YourAssemblyNameFullTypeName"
"RuntimeVersion"="v4.0.30319"
"CodeBase"="file:///C:/Users/Simon/source/repos/VBAIDEAddin/VBAIDEAddin/bin/Debug/VBAIDEAddin.dll"

[HKEY_CLASSES_ROOT\CLSID\1D5F24D3-E24C-4283-AAA9-AAED51B146F7\ProgId]
@="VBAIDEAddin.Connect"