Managing Add-ins for Excel

Managing Add-ins for Excel

List all Addins into a sheet ( COM and XLAM )

Copy and paste the below into a module , when run it'll list all active addins into a table.

This can be a foundation to build other tools, like version testing scripts to ensure the latest instance is loaded for a user.  If you have a network directory or sharepoint folder, these can be checked also.

Sub ShowAllAddins()
Dim oCom As COMAddIn, CmCnt As Integer, OxLam As AddIn

    For Each oCom In Application.COMAddIns
    Range("a" & CmCnt + 1).FormulaR1C1 = "" & oCom.Description
    Range("b" & CmCnt + 1).FormulaR1C1 = "" & oCom.progID
    Range("c" & CmCnt + 1).FormulaR1C1 = "" & oCom.Connect
    Range("d" & CmCnt + 1).FormulaR1C1 = "" & oCom.Parent
    Range("e" & CmCnt + 1).FormulaR1C1 = "" & oCom.GUID
        'If oCom.Description = "Data Transfer Excel Add-in" Then
        '    oCom.Connect = False
        '    oCom.Connect = True
        CmCnt = CmCnt + 1
        '    Exit For
        'End If
    Next oCom
    For Each OxLam In Application.AddIns
    Range("a" & CmCnt + 1).FormulaR1C1 = "" & OxLam.FullName
    Range("b" & CmCnt + 1).FormulaR1C1 = "" & OxLam.progID
    Range("c" & CmCnt + 1).FormulaR1C1 = "" & OxLam.Installed
    Range("d" & CmCnt + 1).FormulaR1C1 = "" & OxLam.IsOpen
    Range("e" & CmCnt + 1).FormulaR1C1 = "" & OxLam.path
    Range("f" & CmCnt + 1).FormulaR1C1 = "" & OxLam.Creator
    Range("g" & CmCnt + 1).FormulaR1C1 = "" & OxLam.Name
    CmCnt = CmCnt + 1
    Next OxLam
End Sub


As an example -  the following sub will loop through all referenced Xlam addins in the application, and search for match vs partial match. 

So for example, I have toolbars versioned by date, the sub will search to enable the latest version ( this can be hard coded, or loaded from an SQL database or network/sharepoint file ), and disable older versions that match the name but not the date.

The code below checks if the latest version ( MyAddin string ) , and if not 'installed', sets it to 'installed', and those that are partial matching ( PartMatchLeft )  are uninstalled. 

Sub LatestVersion(MyAddin As String, Optional PartMatchLeft As String)

Dim ChkAddin As AddIn

For Each ChkAddin In Application.AddIns

If ChkAddin.Name = MyAddin And ChkAddin.Installed = False Then

ChkAddin.Installed = True
MsgBox ChkAddin.Name & " LOADED "

End If

If Left(ChkAddin.Name, Len(PartMatchLeft)) = PartMatchLeft And ChkAddin.Installed = True And ChkAddin.Name <> MyAddin Then

ChkAddin.Installed = False
'AddIns(ChkAddin.Name).Installed = False

MsgBox ChkAddin.Name & " CLOSED/UNLOADED"
End If

Next ChkAddin


This is equivalent to checking/unchecking the tickboxes on the add-ins dialogue. Below Toolbarplaceholder.xlam has been enabled,  Example-Totals-C.xlam and Latest_Function.xlam are disabled ( and will not be available as macros to be run by the user ) 


As a usage example -say you have the following versions



You'd perform the replace like so :

Sub LoadLatestToolbar()

Call LatestVersion("ToolBar(04-MAY-17).xlam", "ToolBar")

End Sub

Where it'll unload/uninstall anything that begins with 'ToolBar' , and load/install the latest. 

This variable could be sourced programmatically from a database call, so the toolbar checked/unloaded dynamically.