Loading Excel Add-Ins at Runtime

When automating Excel, the Excel.exe process should be started using the '/automation' switch. This is done automatically when creating a new Microsoft.Interop.Excel.Application object, however if you want to keep track of the Excel processes created you may have to create the Excel process and then get a reference to the application associated with that process. In this case, the /automation switch must be applied manually.

Starting Excel with the /automation switch tells Excel, among other things, not to load any of the files in the XLStart directory or open any Excel Add-Ins. COM add-ins installed for all users are still loaded.

This streamlines the launching of Excel and makes sure that no unnecessary code is running during automation. There are important cases where you may want to have a specific Excel Add-In like an .XLL or .XLAM loaded however. For example, you may have some library functions you want to leverage defined as UDFs in an XLL or VBA functions in an XLAM.

The following options are available if you want to do this.

1.) Application.RegisterXLL
a. This is a method which can be called from VBA which loads an XLL at a specific location and registers the functions and commands contained in the XLL.
2.) AddIns.Add
a. This is a method which can be called from VBA which loads any type of add-in (XLL, XLA or XLAM). After loading the add-in, perform step 3 to open it.
3.) AddIn.Installed = true
a. Once you have a reference to a loaded add-in, set AddIn.Installed = true to cause the add-in to be opened.
b. Note that add-ins that are known when Excel is started with the /automation switch will already be marked as "Installed", but they are not opened. In this case, Set Installed = false before setting Installed = true

 Private Sub Workbook_Open()
    Dim success As Boolean
    Dim myAddIn As AddIn
    
    ' Load XLL
    success = Application.RegisterXLL("c:\myaddins\myxll.xll")
    
    ' Load and install new XLAM
    Set myAddIn = Application.AddIns.Add("c:\myaddins\myxlam.xlam")
    myAddIn.Installed = True
    
    ' Load known XLAM
    For Each myAddIn In AddIns
        If myAddIn.Name = "myknownaddin.xlam" Then
            myAddIn.Installed = False
            myAddIn.Installed = True
        End If
    Next
End Sub

HPC Services for Excel

 The above strategies are effective for using add-ins on cluster compute nodes and workstation nodes with the HPC Services for Excel in Windows HPC Server 2008 R2.

Comments

• Anonymous
  January 06, 2014
  How can i adjust this macro to make it install and activate multiple addins?And how can I have this macro without the notification "copy this addin to your personal addins folder?"I have the xlam on a shared network drive and for maintenance reasons, I'd like to have it loaded from this location  every time the workbook opens.