Automatically install Excel VBA add-on
If you want to use a commercial tool, Advanced Installer has built-in support for Office add-on installers: http://www.advancedinstaller.com/user-guide/addins-tab.html
Another approach is to use custom code to customize the add-in. Maybe this will help: http://www.cpearson.com/excel/installinganxla.aspx
source to share
I have created an unattended installation routine that will be added to the "This Workbook" section of the XLAM file so that it will automatically start when the file is opened. To distinguish between the installation file and the installed file, the installation version is named ".install.xlam" and the installed version is named ".xlam". (Otherwise Excel has "Sorry, Excel cannot open two workbooks with the same name at the same time."
Procedure: - Rename your XLAM file with .install.xlam - Open it and edit it in Visual Basic Editor (VBE) - Add the following procedures to the "This Workbook" section in VBE - Save the file
To split / install XLAM, now you just need to ask the user to double-click the XLAM file, enable macros as needed, and accept the add-in installation.
If you want to update your XLAM later, you just double-click it, enable macros as needed and refuse to install it. Then edit it and save your changes.
Here's 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
source to share