How can I ensure that an Addin / xll is 'packaged' with C# programmatically created Excel workbooks?

Question

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)?

Thanks

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).

So the below code constructs a String that represents the VBA function (so you have to write it exactly as a working VBA function). In any case, even if it has errors the VBA debugger will throw these up for you so you can debug them as you'll then be in VBA land once it runs. Note the use of .VBProject, Visual Studio will give you the red error so you can then choose to add the correct import.

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