vba 自动安装excel VBA插件

Automatically install excel VBA add-in

提问by anirus

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?

我已经编写了 excel VBA 加载项文件 (.xlam)。我还有一个导出的功能区自定义 (.exportedUI)。如何创建安装程序，以便我的用户只需运行安装程序即可安装 excel VBA 加载项和功能区自定义？

回答by Cosmin Pirvu

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

如果你想要一个商业工具，高级安装程序有一些对 Office 插件安装程序的内置支持：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

另一种方法是使用自定义代码来配置加载项。也许这会有所帮助：http: //www.cpearson.com/excel/installinganxla.aspx

回答by Willy Roche

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

我创建了一个要添加到 XLAM 文件的“本工作簿”部分的自动安装程序，以便在文件打开时自动运行。为了区分安装文件和已安装文件，安装版本命名为“.install.xlam”，安装版本仅命名为“.xlam”。（否则 Excel 会出现“抱歉，Excel 无法同时打开两个同名工作簿”。

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

过程： – 使用 .install.xlam 重命名 XLAM 文件 – 打开它并在 Visual Basic 编辑器 (VBE) 中编辑 – 将以下过程添加到 VBE 的“此工作簿”部分 – 保存文件

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.

为了共享/安装您的 XLAM，您现在只需要求用户双击 XLAM 文件，根据需要启用宏并接受安装插件。

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.

如果您想稍后更新您的 XLAM，您只需双击它，根据需要启用宏并拒绝安装它。然后编辑它并保存更改。

Here is the code to add to “ThisWorkbook”:

这是添加到“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