Excel interop loading XLLs and DLLs

Question

I have excel with the Bloomberg API ( which uses simple calls like =BDP("MS equity","ask") ). I also have a C# application that opens an excel file (through interop) that uses the Bloomberg API. I have read here that addins are not loaded when you load excel through interop. I have even tried using the code suggested there. However, that only seems to work for XLL and XLAM files and it looks like Bloomberg also uses DLL files which doesn't get loaded. Is there any way to get all of these addins to be loaded through interop?

Answer 1

I assume that, if you start excel manually the add-ins are loaded properly.

If so, you can get an Excel Interop Object with loaded BB-Addins by calling Process p = Process.Start("excel.exe"); and then using the AccessibleObjectFromWindow method to get the Interop Object.

An example on how to do that can be found here.

---

Quick walkthrough

I decided to show the steps I went through to get an Excel.Interop.Application instance with loaded bloomberg add ins. Maybe someone looks at this and finds it useful, or knows where I missed something to get a more elegant solution.

I assume you have the bloomberg terminal installed on your client and that you can request reference and historical data (BDP and BDH) out of excel. Otherwise this is probably not for you...

The simplest way doesn't work

var excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(filePath);
excel.Run("RefreshAllStaticData");

Run() will throw a COMException:

Cannot run the macro 'RefreshAllStaticData'. The macro may not be available in this workbook or all macros may be disabled.

Loading AddIns by hand doesn't work

var excel = new Microsoft.Office.Interop.Excel.Application();
var addIn = ex.AddIns.Add(filename);
addIn.Installed = true;
Console.WriteLine(addIn.IsOpen);

false

But if start Excel by klicking on the Windows Taskbar, the AddIns are being loaded nicely... So I tried to start excel by starting the executable file "excel.exe".

Using Process.Start("excel.exe") works!!

Starting excel using the System.Diagnostics.Processclass loads the bloomberg AddIns. (I can see the Bloomberg-RibbonTab.)

Now all I need is the Interop-Object of that excel process. I can get it using the above example

My implementation of it

//From http://blogs.officezealot.com/whitechapel/archive/2005/04/10/4514.aspx
public class ExcelInteropService
{
    private const string EXCEL_CLASS_NAME = "EXCEL7";

    private const uint DW_OBJECTID = 0xFFFFFFF0;

    private static Guid rrid = new Guid("{00020400-0000-0000-C000-000000000046}");

    public delegate bool EnumChildCallback(int hwnd, ref int lParam);

    [DllImport("Oleacc.dll")]
    public static extern int AccessibleObjectFromWindow(int hwnd, uint dwObjectID, byte[] riid, ref Microsoft.Office.Interop.Excel.Window ptr);

    [DllImport("User32.dll")]
    public static extern bool EnumChildWindows(int hWndParent, EnumChildCallback lpEnumFunc, ref int lParam);

    [DllImport("User32.dll")]
    public static extern int GetClassName(int hWnd, StringBuilder lpClassName, int nMaxCount);

    public static Microsoft.Office.Interop.Excel.Application GetExcelInterop(int? processId = null)
    {
        var p = processId.HasValue ? Process.GetProcessById(processId.Value) : Process.Start("excel.exe");
        try
        {
            return new ExcelInteropService().SearchExcelInterop(p);
        }
        catch (Exception)
        {
            Debug.Assert(p != null, "p != null");
            return GetExcelInterop(p.Id);
        }
    }

    private bool EnumChildFunc(int hwndChild, ref int lParam)
    {
        var buf = new StringBuilder(128);
        GetClassName(hwndChild, buf, 128);
        if (buf.ToString() == EXCEL_CLASS_NAME) { lParam = hwndChild; return false; }
        return true;
    }

    private Microsoft.Office.Interop.Excel.Application SearchExcelInterop(Process p)
    {
        Microsoft.Office.Interop.Excel.Window ptr = null;
        int hwnd = 0;

        int hWndParent = (int)p.MainWindowHandle;
        if (hWndParent == 0) throw new ExcelMainWindowNotFoundException();

        EnumChildWindows(hWndParent, EnumChildFunc, ref hwnd);
        if (hwnd == 0) throw new ExcelChildWindowNotFoundException();

        int hr = AccessibleObjectFromWindow(hwnd, DW_OBJECTID, rrid.ToByteArray(), ref ptr);
        if (hr < 0) throw new AccessibleObjectNotFoundException();

        return ptr.Application;
    }
}

Now I can use the following line of code to get an Excel.Interop.Application instance with loaded bloomberg addins!

var excel = ExcelInteropService.GetExcelInterop();
excel.Workbooks.Open(filename);
excel.Run("RefreshAllStaticData");
// works "like a charm"

I hope this spares someone the hours I wasted with this and if someone has a more elegant solution and wants to share it, it would be much appreciated.

Answer 2

If you want to load a DLL into Excel addins, you must register this DLL file using RegAsm. And check HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Excel\AddIns to see whether it's registered successfully.

Answer 3

I was able to get a similar result using NetOffice. This particular function will retrieve the last started instance of the excel process or create a new one if one does exist. From there you are free to open/create a new workbook.

The nice thing about using netoffice and this approach is that it doesn't depend on version specific interop DLLs and if you dispose of the NetOffice objects then all of the excel COM objects are cleaned up properly.

using Xl = NetOffice.ExcelApi;    
private static Xl.Application GetOrStartExcel(bool started = false)
{
    Xl.Application application = null;

    try
    {
        object nativeProxy = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
        application = new Xl.Application(null, nativeProxy);
        return application;
    }
    catch (Exception ex)
    {
        if (!started) Process.Start("excel.exe");
        Thread.Sleep((int) TimeSpan.FromSeconds(1).TotalMilliseconds);
        return GetOrStartExcel(true);
    }
}

Usage:

using (var excel = GetOrStartExcel())
{
    //Do work with excel object
}

Answer 4

I realize that it's been while since this question has been asked. Nevertheless I want to share my experience as I came across the same problem. An easy solution is to proceed as suggested in https://blogs.msdn.microsoft.com/accelerating_things/2010/09/16/loading-excel-add-ins-at-runtime/ by first setting .Installed to false and then to true:

this._application = new Application
                {
                    EnableEvents = true,
                    Visible = true
                };

                //Load Bloomberg Add-In
                for (var i = 1; i <= this._application.AddIns.Count; i++)
                {
                    var currentAddIn = this._application.AddIns.Item[i];

                    if (currentAddIn.Name != "BloombergUI.xla") continue;

                    currentAddIn.Installed = false;
                    currentAddIn.Installed = true;
                }