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?

Piane

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.

Metaphase

It is a mess, but it seems to work when I step through with the debugger, but not when I run it without debugging. – Piane

that's because the excel window isn't ready when you run the program... but if you use the debugger, and step over the Process.Start(...)-Method Excel has time to load it's main window and by the time you get to the function that retreives your application object for you, the WindowHandle - that has a reference to the Application object on it - has been initialized... you need to stop and wait for excel to load the MainWindow... Use Thread.Sleep(...) or do some hack with a MessageBox and let the user click when the Excel Instance is ready... It's a mess!! X-( – Metaphase

A possible solution could be to write an Excel-AddIn in VisualStudio that acts as an Interop-Object-Server that allows you to ask if all Addins have been loaded and if the WindowHandle is initialized. Through NamedPipes or WindowsMessaging or IOStreams or what ever asynchronous mechanism your Client would know exactly when the instance is ready and then you run AccessibleObjectFromWindow(...). – Metaphase

I assumed the window handle was the problem. I think the best solution is to have a plugin in excel do the work for me and pass the results to my program. Thanks for the help though. This thing has been painful! – Piane

This worked for me in conjunction with using late-binding to get the Excel instance to clean up SearchExcelInterop. You're the man for sharing! – Tragic

Loading AddIns by hand doesn't work - but does it work if you unload them first? – Duodenal

Answer 2

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;
                }

Ignominy

Thank you Sebastian - such an easy approach and so hard to find! – Stumpage

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
}

Piane

Answer 4

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.

Valorous

I want to load the addin, which is a DLL, when I start excel through COM-interop with C#. What you are suggesting doesn't do this at all. – Piane