c# - 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.