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

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 2

I was facing issues with loading user defined function [UDF] in VBA code. Was able to load xll file but wasn't able to call.

Following code successfully loads XLL files and let's call user defined function from VBA code. There may be redudendent instruction in this module but it works!

Sub InstallAddIn()

On Error GoTo ErrorHandle

Application.DefaultFilePath = "D:\\MyFolder"

Application.RegisterXLL ("MyXLLFileName.xll")

Set AI = AddIns.Add(Filename:="D:\MyFolder\MyXLLFileName.xll")

    If AddIns("MyXLLFileName").Installed Then
       LogInformation ("My XLL is installed")
    Else
       LogInformation ("My XLL is NOT installed")
    End If
Exit Sub

ErrorHandle:

LogInformation ("------------------------") 'Logging function that I have written. Not a std api

LogInformation (Err.HelpFile)

LogInformation (Err.HelpContext)

LogInformation (Err.Description)

LogInformation ("Error in InstallAddIn module")

LogInformation ("------------------------")

End

End Sub

Answer 3

A proper solution would be :

1- save the current directory with

 string CurrentDir =   Directory.GetCurrentDirectory()

2- then you use

  Directory.SetCurrentDirectory(dirXll);

where dirXll is the location of your xll (cf GetExecutingAssembly()) .

3- Load your xll (RegisterXLL)

4- and finaly use CurrentDir to set the current directory back to its original location.

Don't forget to add all dlls that you xll is relyong on in the same folder as your xll.

Answer 4

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");

Answer 5

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.