Tuesday, March 27, 2007

Fun with XLL

The oldest comment I found while poking around the XLL project is dated Nov, 1996, a bit over ten years ago. I probably wrote my "hello, world" in C around the same time. Anyway, the goal is to call the excel plugin functions in C#/.NET. This should be straight forward, at least in theory: XLL files are merely DLLs with a few special functions. There is no reason they can't be loaded and called via DllImport. However, the following code throws a DllNotFoundException, and GetLastErr promptly returns no error code:

  [DllImport("myxll.xll", EntryPoint="GetVersion")]
  public static extern string XLGetVersion();
Digging a bit deeper with Depends, I found an excel library called XLCALL32.DLL, referenced by the XLL is the cause of trouble. The library can't be loaded with LoadLibrary outside Excel. I suspect that has something to do with the mysterious looking importing variable shown in the right panel below.A suprisingly simple solution is to manually pre-load XLCALL32.DLL with LoadLibraryEx and choose NOT to resolve any external references:
  IntPtr ptr = LoadLibraryEx("XLCALL32.DLL", 
Then the old P/Invoke trick turns back on (because XLCALL32 has been loaded). This obviously won't work if the XLL contains callback functions into Excel. But for everything else, there is DllImport... And here is a reference to the structure of XLL files. (link to complete e-book).