Ensuring XLL is packaged with my C# created Excel workbooks

Question

Hi

I'm busy debugging a growing C# Windows sevice I'm making.

It does:

1) Background reads to a SQL Server db and receives 'jobs'.

2) The jobs are XML (1..n reports containing 1..n tabs containing 1..n pivots containing 1..n where/slicers, 1..n measures, 1..n dimensions)

3) The C# then goes through these XML jobs and creates Excel workbooks.

Note: the pivots are actually a call to my custom xll/dll for talking to my remote Java ActivePivot cube.

The Excel workbooks are saving to a network share and the formulae are embedded fine as array functions (.FormulaArray = etc).

I've tried .RegisterXLL etc but I think this not really adding it where I want it anyway.

Do I need to programmatically add VBA OnOpen style code like this: Is there a way to add vba macro code to excel?

Or is there a nicer modern C# way to ensure a ref/link to the XLL/DLL (path to it etc)?

The workbooks will be opened by users from the share where my C# service is saving them to.

At the moment if the newly waved workbook is opened, it has test function calls in it but they have NAME? etc as it cannot see the xll.

Thanks

Leigh Tilley

http://tilleytech.com

Answer 1

I seem to have a habit of asking questions then sometime later answering them!

I noted @DS_London's suggestion but before I tried it, I actually looked at the link I referenced in my question (about injecting VBA).

(See question StackOverflow - https://stackoverflow.com/questions/67873692/how-can-i-ensure-that-an-addin-xll-is-packaged-with-c-sharp-programmatically/68022533#68022533)

With no C# approach being found/obvious I did this and it works. The functions in my XLLs are seen/usable in the C# programmatically produced workbooks. I have to manually enable security/macros when I open it and manually refresh the function (I am thinking of ways around this):

var codeText = "Private Sub Workbook_Open()\r\n";
codeText += "  Dim ourAddin As Object" + "\r\n"; 
codeText += "  Application.RegisterXLL (\"" + addin + "\")" + "\r\n";
codeText += "  Set ourAddin = Application.AddIns.Add(\"" + addin + "\",True)" + "\r\n";
codeText += " ourAddin.Installed = True" + "\r\n";
codeText += "End Sub";
var workbookMainModule = workbook.VBProject.VBComponents.Item("ThisWorkbook");
workbookMainModule.CodeModule.AddFromString(codeText);