Automatically install excel VBA add-in

Question

I have written excel VBA add-in file (.xlam). I also have a exported ribbon customization (.exportedUI). How can I create an installer so that my users can just run the installer to install the excel VBA add-in and the ribbon customization?

Answer 1

The way I have done it is to have the user run a batch file that copies the file (I have it stored on a server, but it can be part of a self extracting zipeed file I guess), that copies the XLAM file into the Microsoft add-in default folder.

Then you need to activate the add-in (once) from file>options>add-in The user will then have the add-in available.

The pseudo I use for the batch is this: hope it helps.

SET $Path2PlugIn=C:\Users\%USERNAME%\AppData\Roaming\Microsoft\AddIns\
SET $PathOrig="\\PathToServerWhereAddInSits\"

COPY %$PathOrig%AddInName.xlam %$Path2PlugIn%AddInName.xlam 

Answer 2

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”:

‘ (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

Option Explicit
Const bVerboseMessages = False ‘ Set it to True to be able to Debug install mechanism
Dim bAlreadyRun As Boolean ‘ Will be use to verify if the procedure has already been run

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 3

If you want a commercial tool, Advanced Installer has some built-in support for Office Add-in installers: http://www.advancedinstaller.com/user-guide/addins-tab.html

Another approach is using custom code to configure the add-in. Perhaps this will help: http://www.cpearson.com/excel/installinganxla.aspx