Excel VBA Load Addins

Question

I am trying to create a new instance of Excel using VBA using:

Set XlApp = New Excel.Application

The problem is that this new instance of Excel doesn't load all the addins that load when I open Excel normally...Is there anything in the Excel Application object for loading in all the user-specified addins?

I'm not trying to load a specific add-in, but rather make the new Excel application behave as though the user opened it themself, so I'm really looking for a list of all the user-selected add-ins that usually load when opening Excel.

Answer 1

Try:

Set XlApp = CreateObject("Excel.Application")

Answer 2

Using CreateObject("Excel.Application") would have the same result as using New Excel.Application, unfortunately.

You will have to load the Addins that you need individually by file path & name using the Application.Addins.Add(string fileName) method.

Answer 3

I looked into this problem again, and the Application.Addins collection seems to have all the addins listed in the Tools->Addins menu, with a boolean value stating whether or not an addin is installed. So what seems to work for me now is to loop through all addins and if .Installed = true then I set .Installed to False and back to True, and that seems to properly load my addins.

Function ReloadXLAddins(TheXLApp As Excel.Application) As Boolean

    Dim CurrAddin As Excel.AddIn

    For Each CurrAddin In TheXLApp.AddIns
        If CurrAddin.Installed Then
            CurrAddin.Installed = False
            CurrAddin.Installed = True
        End If
    Next CurrAddin

End Function