1

If I open a blank Excel spreadsheet, open the VBA editor, and run the following:

Sub x()
    For Each a In AddIns()
        Debug.Print a.Title & " : " & a.Name
    Next
End Sub

I get the following output:

Analysis ToolPak : ANALYS32.XLL
Analysis ToolPak - VBA : ATPVBAEN.XLAM
Euro Currency Tools : EUROTOOL.XLAM
Solver Add-in : SOLVER.XLAM

However, if I then go to File > Options > Add-ins, I see a longer list of add-ins:

Analysis ToolPak : ANALYS32.XLL
Analysis ToolPak - VBA : ATPVBAEN.XLAM
Date (XML) : MOFL.DLL
Euro Currency Tools : EUROTOOL.XLAM
Microsoft Power Map for Excel : EXCELPLUGINSHELL.DLL
Solver Add-in : SOLVER.XLAM
MyAddinFoo : Foo.DLL

Why does this happen? Why does AddIns() only detect a subset of addins?

So that this doesn't end up an XY question, let me describe what I want to do: I have an add-in (MyAddinFoo) which offers spreadsheet functions.

I'm trying to automate a spreadsheet using a VB script which calls Excel as an OLE object and runs a VBA macro on the spreadsheet. I already have a script which does this just fine:

'test.vbs
Set objExcel = CreateObject("Excel.Application")

objExcel.Application.Run "'foo.xlsm'!Module1.FooMacro"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing

The only problem I have is that the spreadsheet relies on this add-in. When Excel is run as an OLE object, add-ins must be manually activated. However, since my add-in isn't found by Addins(), I can't use AddIns("MyAddinFoo").Installed = True in the script to activate it.

So, to put my question another way, how can I activate my add-in via a VB script when it's not found by AddIns()?

EDIT: After @BigBen's comment regarding COMAddins(), I can confirm it does find the missing add-ins, including mine.

However, I can't see how to use it, since the script says the add-in is already connected:

'test.vbs
Set objExcel = CreateObject("Excel.Application")
Wscript.Echo objExcel.Application.COMAddIns("MyAddinFoo.Connect").Connect
Set objExcel = Nothing

This outputs -1, the integer representation of True.

So if the add-in is already connected, I don't know what else to do to make it actually operate on the spreadsheet via the script.

braX
  • 11,506
  • 5
  • 20
  • 33
Wasabi
  • 2,879
  • 3
  • 26
  • 48
  • 1
    There are also [`COMAddIns`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.comaddins) - Microsoft Power Map is one for example. – BigBen Oct 31 '19 at 19:36
  • [This question](https://stackoverflow.com/questions/48565694/enable-com-addins-in-excel-through-vba) may be useful (note, untested with `CreateObject`). – BigBen Oct 31 '19 at 19:47
  • @BigBen, please see my edit. Indeed, `COMAddIns` does find my add-in, but it's apparently already connected to my Excel OLE object, so I don't know why it's not actually doing anything to my spreadsheet. – Wasabi Oct 31 '19 at 20:08
  • Do you maybe have to toggle the connection, similar to [this question](https://stackoverflow.com/questions/213375/loading-addins-when-excel-is-instantiated-programmatically)? – BigBen Oct 31 '19 at 20:20

0 Answers0