Loading addins when Excel is instantiated programmatically

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.

Optimum

Answer 1

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

Optimum

+1 on your own question! Kind of like cheating. ;-) Nice answer though, this definitely adds to the body of knowledge. – Expiration

Yes, this is a workaround for the "AddIns not loaded" problem – Orsini

Been annoyed at this one for far too long. Nice answer. – Appledorf

how can I use this function? I am getting a VALUE error when I insert this in a sheet. – Lodgings

You can't use this function from within a spreadsheet. You can try removing the TheXLApp variable from the function declaration, and instead of using TheXLApp in the body of the routine, use Application. I don't know that it'll work, though. You might also modify it to return a numeric value so that you can have it only run the function if it hasn't run yet. – Optimum

Just as a word of warning, if you're working with multiple instances of Excel, this approach CAN leave your addins disconnected on an instance that previously had them enabled. It seems to depend on what sort of addin is being loaded, though. – Leviticus

What do I pass in for the argument when I call the function? Could you explain the first line. Why are you creating an application object? I am opening excel from vbscript, so should I use the same excel application object that I create there? (instead of TheXLApp) – Narcosis

@OligarchicTendencies you just pass in the excel application object you are working with. I wrote this as a function because my application starts Excel in a couple different ways depending on what we're doing, so I just call this when I start an Excel application – Optimum

This also works for Word Addins, after Set oWord = CreateObject("Word.Application"). Just change Excel to Word in the function. – Pushy

for what it's worth, rather than setting installed to false and back to true in order to open the addin, i just open the addin -- TheXLApp.Workbooks.Open CurrAddin.FullName – Teddi

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.

Expiration

This is very helpful for my situation, where I am launching Excel and I want an Add-In that is not normally loaded to be active. – Jink

Answer 3

I'm leaving this answer here for anyone else who ran into this problem, but using JavaScript.

A little background... In my company we have a 3rd party web app that used JavaScript to launch Excel and generate a spreadsheet on the fly. We also have an Excel add-in that overrides the behavior of the Save button. The add-in gives you the option of saving the file locally or in our online document management system.

After we upgraded to Windows 7 and Office 2010, we noticed a problem with our spreadsheet-generating web app. When JavaScript generated a spreadsheet in Excel, suddenly the Save button no longer worked. You would click save and nothing happened.

Using the other answers here I was able to construct a solution in JavaScript. Essentially we would create the Excel Application object in memory, then reload a specific add-in to get our save button behavior back. Here's a simplified version of our fix:

function GenerateSpreadsheet()
{
    var ExcelApp = getExcel();
    if (ExcelApp == null){ return; }

    reloadAddIn(ExcelApp);

    ExcelApp.WorkBooks.Add;
    ExcelApp.Visible = true;
    sheet = ExcelApp.ActiveSheet;

    var now = new Date();
    ExcelApp.Cells(1,1).value = 'This is an auto-generated spreadsheet, created using Javascript and ActiveX in Internet Explorer';

    ExcelApp.ActiveSheet.Columns("A:IV").EntireColumn.AutoFit; 
    ExcelApp.ActiveSheet.Rows("1:65536").EntireRow.AutoFit;
    ExcelApp.ActiveSheet.Range("A1").Select;

    ExcelApp = null;
}

function getExcel() {
   try {
       return new ActiveXObject("Excel.Application");
   } catch(e) {
       alert("Unable to open Excel. Please check your security settings.");
       return null;
   }
}

function reloadAddIn(ExcelApp) {
    // Fixes problem with save button not working in Excel,
    // by reloading the add-in responsible for the custom save button behavior
    try {
        ExcelApp.AddIns2.Item("AddInName").Installed = false;
        ExcelApp.AddIns2.Item("AddInName").Installed = true;
    } catch (e) { }
}

Papillose