Managing Add-ins for Excel

By John — in addins, macro, macro distribution, ribbon, xlam · 3 min read
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
Vb

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


'CHECK MATCH ADDIN, IF NOT LOADED, LOAD/ENABLE
If ChkAddin.Name = MyAddin And ChkAddin.Installed = False Then

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

End If

'UNLOAD / DISABLE NON-MATCHING BUT PARTIAL MATCH ADDINS
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
Vb

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 ) 

addins-checkbox1

As a usage example -say you have the following versions

ToolBar(25-JAN-17).xlam
ToolBar(06-FEB-17).xlam
ToolBar(04-MAY-17).xlam

 

You'd perform the replace like so :


Sub LoadLatestToolbar()

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

End Sub
Vb

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. 

Tagged as addins, macro, macro distribution, ribbon and xlam

John —

Started out in media/graphic design then moved to Financial Services / MI data / Business intelligence. ~15 years experience of Excel / VBA , and more recently Qliksense , SQL

  • [CONTACT]
  • [LOCATION]
    London, UK