Can't load xll programmatically

Question

I'm trying to automating some tests for an Excel add-in, which is in xll form. I have some problem in loading the xll. I'm writing it in C# and my code looks like this:

---

using Microsoft.Office.Interop.Excel;

Application xlApp;
Workbook xlWorkBook;
Worksheet xlWorkSheet;

// create application, open workbook, etc ...
// now try to register xll
xlApp.RegisterXLL("C:\\SomePath\\Whatever.xll");

---

However, this always return false. I try to see what Excel secretly does when I load the xll manually by recording the macro. The macro looks like:

---

Sub Macro1()
ChDir "C:\SomePath"
Application.RegisterXLL Filename:= _
"C:\SomePath\Whatever.xll"
End Sub

---

The only difference seems to be the ChDir, so I changed my code to:

---

FileSystem.ChDir("C:\\SomePath");
xlApp.RegisterXLL("C:\\SomePath\\Whatever.xll");

---

But it still doesn't work. Another odd thing is when I put a breakpoint before the RegisterXLL line and load the xll manually first, the RegisterXLL method will return true. But otherwise, it will return false.

Answer 1

Yes, the ChDir command can be important. It helps Windows find any DLLs that whatever.xll depends on. The reason it doesn't solve your problem is that FileSystem.ChDir() changes the working directory for your test program, not for Excel.

Not a wholeheckofalot you can do. Deploying the xll do a directory that's on the system's PATH will solve it. A pragmatic solution is to just run that macro.

Answer 2

I know this is not a direct answer to your question, but you may want to look at using VSTO within Visual Studio. VSTO automates a lot of these types of issues. The version in VS 2010 is so much better than what they used to have and you can built application level add-ins as opposed to just document level add-ins. If you need user defined functions you can use a COM add-in as described here:

http://blogs.officezealot.com/whitechapel/archive/2005/04/10/4514.aspx

It was originally based on this article:

http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx

We use a combination of VSTO for our main application, and the COM add-in for user defined functions. What's nice about that is that they are loaded in the same app domain so they can talk to each other.

Answer 3

Thanks for all the suggestions.

I solved the problem by changing the default file path for the Excel application.

Application.xlApp = new ApplicationClass();
xlApp.DefaultFilePath = "C:\\SomePath";
xlApp.RegisterXLL("Whatever.xll");