如何使用 msi 安装程序部署 VBA Excel 加载项 (foo.xlam)？

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

提问by Frank

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.

我是一名 C# 开发人员，他将同事的 VBA Excel 加载项（.xlam 文件）与我的 msi 安装程序（如果重要的话，使用 VS 部署项目构建）捆绑在一起。.xlam 位于应用程序文件夹 (C:\Program Files (x86)\MyCompany\TheProduct) 目录中。用户被迫导航到 Excel 选项 > 加载项 > 管理 Excel 加载项转到... > 浏览，然后被迫导航到上面列出的安装目录。浏览屏幕默认目录是 %APPDATA%\Microsoft\AddIns。

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

有没有办法让我无需单击即可自动启用此 VBA 加载项？

Thanks in advance,

提前致谢，

Frank

坦率

采纳答案by Cosmin Pirvu

Windows Installer doesn't have direct support for this. So either you use some custom actions or you buy a tool which offers direct support for installing Office add-ins.

Windows Installer 对此没有直接支持。因此，要么使用某些自定义操作，要么购买一个工具，该工具为安装 Office 加载项提供直接支持。

回答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”的代码：

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

回答by Stein ?smul

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.

这通常涉及更新注册表的 HKCU 部分（这是在 Excel 选项中手动检查加载项时发生的情况）。但是，我有时会使用 Excel 安装目录中的 XLStart 文件夹。如果您的插件类型正确，Excel 将在启动时为系统上的所有用户加载它，并且他们无法将其关闭。有时这是可以接受的。明智的部署要容易得多。

回答by Alban Lopez

you can insert this code in your *.xlam in the sheet "ThisWorkBook" this code install and activate the current AddIns, just by opening

您可以将此代码插入到“ThisWorkBook”表中的 *.xlam 中，此代码安装并激活当前的加载项，只需打开

Private Sub Workbook_Open()
    Dim oXL As Object, oAddin As Object
    URL = Me.Path & "\"
    normalUrl = Application.UserLibraryPath ' Environ("AppData") & "\Microsoft\AddIns"
    AddinTitle = Mid(Me.Name, 1, Len(Me.Name) - 5)

    If URL <> normalUrl Then
        If MsgBox("Can you Install AddIns ?", vbYesNo) = vbYes Then
            Set oXL = Application ' CreateObject("Excel.Application")
            oXL.Workbooks.Add
            Me.SaveCopyAs normalUrl & Me.Name
            Set oAddin = oXL.AddIns.Add(normalUrl & Me.Name, True)
            oAddin.Installed = True

            oXL.Quit
            Set oXL = Nothing
        End If
    End If
End Sub