gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 14:55:39 28 4
gpt4 key购买 nike

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

有没有办法让我无需点击即可自动启用此 VBA 插件?

提前致谢,

弗兰克

最佳答案

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

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

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

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

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

关于excel - 如何使用 msi 安装程序部署 VBA Excel 加载项 (foo.xlam)?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6308744/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com