Excel interacts with XLL and DLL

Question

I have excellence with the Bloomberg API (which uses simple calls like = BDP ("MS equity", "ask"). I also have a C # application that opens an excel file (via interop) that uses the Bloomberg API. I read here that add is not loading when loading excel via interop. I even tried using the code suggested there. However, it seems that this only works for XLL and XLAM files, and it seems that Bloomberg also uses DLL files that do not load. Is there a way to get all these add-ons to download via interop?

Answer 1

I assume that if you run excel manually, add-ons load properly.

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

An example of how to do this can be found here .

---

Quick walkthrough

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

I assume that you have a bloomberg terminal installed on your client and you can request links and historical data (BDP and BDH) from excel. Otherwise, this is probably not for you ...

The easiest way does not work

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

Run() will COMException :

Cannot start the RefreshAllStaticData macro. A macro may not be available in this book, or all macros may be disabled.

Manual AddIns loading does not work

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

falsely

But if you start Excel using klicking on the Windows taskbar, then AddIns load beautifully ... Therefore, I tried to run excel by running the excel.exe executable.

Powered by Process.Start ("excel.exe").

Running excel using the System.Diagnostics.Process class loads Bloomberg add-ons. (I see Bloomberg-RibbonTab .)

Now I only need the Interop-Object of this excel process. I can get it using the above example

My implementation

 //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 instance of Excel.Interop.Application with bloomberg add-ons loaded!

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

I hope that this will save someone from the hours that I wasted with it, and if someone has a more elegant solution and wants to share it, it would be very grateful.

Answer 2

I managed to get a similar result using NetOffice . This particular function will retrieve the last running instance of the excel process or create a new one if one exists. From there you can open / create a new book.

The good thing about using netoffice and this approach is that it does not depend on version-compatible files, and if you manage NetOffice objects, then all 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); } } 

Using:

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

Answer 3

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