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.

score 30 · Accepted Answer · answered Apr 30 '09 at 12:45

30

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

answered Apr 30 '09 at 12:45

Jon Fournier

4,089
3
30
41

+1 on your own question! Kind of like cheating. ;-) Nice answer though, this definitely adds to the body of knowledge. – Mike Rosenblum Oct 11 '09 at 01:37
Yes, this is a workaround for the "AddIns not loaded" problem – A9S6 Dec 04 '09 at 08:05
Been annoyed at this one for far too long. Nice answer. – JDunkerley Dec 17 '12 at 09:11
how can I use this function? I am getting a VALUE error when I insert this in a sheet. – JoaMika May 04 '14 at 17:08
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. – Jon Fournier May 05 '14 at 13:41
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. – tobriand Apr 20 '16 at 08:51
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) – ThomasRones Nov 16 '17 at 12:00
1

@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 – Jon Fournier Nov 17 '17 at 14:47
This also works for **Word** Addins, after `Set oWord = CreateObject("Word.Application")`. Just change `Excel` to `Word` in the function. – Andre Aug 07 '19 at 10:51

Mike Rosenblum · Answer 2 · 2008-10-17T22:24:19.873

6

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.

edited Oct 17 '08 at 22:24

answered Oct 17 '08 at 22:10

Mike Rosenblum

11,708
6
46
64

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. – 4AM May 03 '17 at 13:01

score 2 · Answer 3 · answered Jun 06 '13 at 18:43

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) { }
}

score -3 · Answer 4 · edited Jun 03 '12 at 03:40

-3

Try:

Set XlApp = CreateObject("Excel.Application")

edited Jun 03 '12 at 03:40

Siddharth Rout

137,434
15
189
237

answered Oct 17 '08 at 21:32

Ken Paul

5,501
2
28
33

Loading addins when Excel is instantiated programmatically

4 Answers4

Linked

Related