Reference programmatically in VBA multiple COM dlls inside an Excel-DNA XLL

Question

So I am using Excel-DNA to pack two COM dlls inside one XLL.

<ExternalLibrary Path="FancyDna.DllOne.dll" ExplicitExports="false" LoadFromBytes="true" ComServer="true" Pack="true" />
<ExternalLibrary Path="FancyDna.DllTwo.dll" ExplicitExports="false" LoadFromBytes="true" ComServer="true" Pack="true" />

And in VBA:

Sub TestEarlyBound()
    Application.RegisterXLL Application.ActiveWorkbook.Path & "\build\FancyDna.Pack-AddIn64-packed.xll"
    ThisWorkbook.VBProject.References.AddFromFile Application.ActiveWorkbook.Path & "\build\FancyDna.Pack-AddIn64-packed.xll"
    Call CallDllOne
    Call CallDllTwo
End Sub

However, when I add the references in VBA using ThisWorkbook.VBProject.References.AddFromFile, only one Dll is referenced with checkbox ticked. The other dll shows up in the Tools --> References after doing that but its checkbox is not checked!

I saw it here that tlb's can be packed in XLL which in return can be referenced in VBA to get full early binding support.

It would be really cool to pack several Dlls in one XLL and also register them programmatically for early binding with intellisense and everything. What am I doing wrong here?

score 1 · Accepted Answer · answered Jun 13 '19 at 18:13

Only a single .tlb file can be embedded in the .xll.

So what you need to do is generate a single .tlb file from the multiple .NET assemblies. This will take some effort – I don’t know of a tool that will do this easily. See this StackOverflow question: Merging two .IDL files or two .tlb files into one file

Maybe try to generate .idl files and merge these as text before generating the .tlb?
Or else use something like ILMerge to combine the assemblies, then run TlbExp on the combined assembly.
Or else find the source for TlbExp and make it work with multiple assemblies.

Let us know if you try any of these!

score 0 · Answer 2 · answered Jun 24 '19 at 14:43

So based on the answer from @Govert, I tried out the following and could make it work...

From each TLB, extract the IDL definition by using OleWoo or OleViewer. OleViewer has to be started with admin privileges, does not contain syntax errors like OleWoo and includes the necessary imports.
Create a new master IDL file and paste all the definitions inside the library block of each IDL inside the library block of the master IDL.
Feel free to name the library namespace inside the master IDL to whatever as the namespace here somehow doesn't matter and you can still create those COM types defined inside this IDL if the types are properly registered with the right DLL location.
Use the midl tool to covnert the IDL file into TLB:

midl {Master IDL}.idl /tlb {Master IDL}.tlb

Reference programmatically in VBA multiple COM dlls inside an Excel-DNA XLL

2 Answers2