How do I deploy a VBA Excel Add-In (foo.xlam) using an msi installer?

Question

I am a C# developer who is bundling a colleague's VBA Excel Add-In (.xlam file) with my msi installer (built using a VS Deployment project if that matters). The .xlam is placed in the application folder (C:\Program Files (x86)\MyCompany\TheProduct) directory. Users are forced to navigate to Excel Options > Add-Ins > Manage Excel Add-Ins Go... > Browse and are then forced to navigate to the install directory listed above. The Browse screen default directory is %APPDATA%\Microsoft\AddIns.

Is there a way for me to automatically enable this VBA Add-In without all of the clicking?

Thanks in advance,

Frank

Answer 1

I created an automatic install procedure to be added to the “This Workbook” section of the XLAM file, so that it’s automatically run when file is open. In order to differentiate the installation file and the installed file, the install version is named “.install.xlam” and the installed version is just named “.xlam”. (Otherwise Excel has an “Sorry, Excel can’t open two workbooks with the same name at the same time.”

Procedure: – Rename your XLAM file with .install.xlam – Open it and edit in Visual Basic Editor (VBE) – Add the following procedures to the “This workbook” section in VBE – Save your file

In order to share/install your XLAM, you now just have to ask the user to double-click the XLAM file, enable macros as needed and accept to install the Add-In.

If you want to update your XLAM later on, you just double-click it, enable macros as needed and refuse to install it. Then edit it and save the changes.

Here is the code to add to “ThisWorkbook”:

Option Explicit
'    (c) Willy Roche (willy.roche(at)centraliens.net)
'    Install procedure of XLAM (library of functions)
'    This procedure will install a file name .install.xlam in the proper excel directory
'    The install package will be name
'    During install you may be prompt to enable macros (accept it)
'    You can accept to install or refuse (which let you modify the XLAM file macros or install procedure

' Set it to True to be able to Debug install mechanism
Const bVerboseMessages = False

' Will be use to verify if the procedure has already been run
Dim bAlreadyRun As Boolean

Private Sub Workbook_Open()
    ' This sub will automatically start when xlam file is opened (both install version and installed version)
    Dim oAddIn As Object, oXLApp As Object, oWorkbook As Workbook
    Dim i As Integer
    Dim iAddIn As Integer
    Dim bAlreadyInstalled As Boolean
    Dim sAddInName As String, sAddInFileName As String, sCurrentPath As String, sStandardPath As String

    sCurrentPath = Me.Path & "\"
    sStandardPath = Application.UserLibraryPath ' Should be Environ("AppData") & "\Microsoft\AddIns"
    DebugBox ("Called from:'" & sCurrentPath & "'")

    If InStr(1, Me.Name, ".install.xlam", vbTextCompare) Then
        ' This is an install version, so let’s pick the proper AddIn name
        sAddInName = Left(Me.Name, InStr(1, Me.Name, ".install.xlam", vbTextCompare) - 1)
        sAddInFileName = sAddInName & ".xlam"


        ' Avoid the re-entry of script after activating the addin
        If Not (bAlreadyRun) Then
            DebugBox ("Called from:'" & sCurrentPath & "' bAlreadyRun = false")
            bAlreadyRun = True ' Ensure we won’t install it multiple times (because Excel reopen files after an XLAM installation)

            If MsgBox("Do you want to install/overwrite '" & sAddInName & "' AddIn ?", vbYesNo) = vbYes Then
                ' Create a workbook otherwise, we get into troubles as Application.AddIns may not exist
                Set oXLApp = Application
                Set oWorkbook = oXLApp.Workbooks.Add
                ' Test if AddIn already installed
                For i = 1 To Me.Application.AddIns.Count
                    If Me.Application.AddIns.Item(i).FullName = sStandardPath & sAddInFileName Then
                        bAlreadyInstalled = True
                        iAddIn = i
                    End If
                Next i

                If bAlreadyInstalled Then
                    ' Already installed
                    DebugBox ("Called from:'" & sCurrentPath & "' Already installed")
                    If Me.Application.AddIns.Item(iAddIn).Installed Then
                        ' Deactivate the add-in to be able to overwrite the file
                        Me.Application.AddIns.Item(iAddIn).Installed = False
                        Me.SaveCopyAs sStandardPath & sAddInFileName
                        Me.Application.AddIns.Item(iAddIn).Installed = True
                        MsgBox ("'" & sAddInName & "' AddIn Overwritten")
                    Else
                        Me.SaveCopyAs sStandardPath & sAddInFileName
                        Me.Application.AddIns.Item(iAddIn).Installed = True
                        MsgBox ("'" & sAddInName & "' AddIn Overwritten & Reactivated")
                    End If
                Else
                    ' Not yet installed
                    DebugBox ("Called from:'" & sCurrentPath & "' Not installed")
                    Me.SaveCopyAs sStandardPath & sAddInFileName
                    Set oAddIn = oXLApp.AddIns.Add(sStandardPath & sAddInFileName, True)
                    oAddIn.Installed = True
                    MsgBox ("'" & sAddInName & "' AddIn Installed and Activated")
                End If

                oWorkbook.Close (False) ' Close the workbook opened by the install script
                oXLApp.Quit ' Close the app opened by the install script
                Set oWorkbook = Nothing ' Free memory
                Set oXLApp = Nothing ' Free memory
                Me.Close (False)
            End If
        Else
            DebugBox ("Called from:'" & sCurrentPath & "' Already Run")
            ' Already run, so nothing to do
        End If

    Else
        DebugBox ("Called from:'" & sCurrentPath & "' in place")
        ' Already in right place, so nothing to do
    End If
End Sub

Sub DebugBox(sText As String)
If bVerboseMessages Then MsgBox (sText)
End Sub

Answer 2

This normally involves updating the HKCU section of the registry (this is what happens when you manually check the addin in the Excel options). However, I sometimes use the XLStart folder in Excel's installation directory. Provided your addin is the right type it will be loaded by Excel on launch for all users on the system, and they have no option to turn it off. Sometimes this is acceptable. Deployment wise it is much easier.