vba 打包和分发 Excel 应用程序的最佳方式是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29630/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the best way to package and distribute an Excel application
提问by JonnyGold
I've writen an Excel-based, database reporting tool. Currentely, all the VBA code is associated with a single XLS file. The user generates the report by clicking a button on the toolbar. Unfortunately, unless the user has saved the file under another file name, all the reported data gets wiped-out.
我编写了一个基于 Excel 的数据库报告工具。当前，所有 VBA 代码都与单个 XLS 文件相关联。用户通过单击工具栏上的按钮生成报告。不幸的是，除非用户将文件保存在另一个文件名下，否则所有报告的数据都会被清除。
When I have created similar tools in Word, I can put all the code in a template (.dot) file and call it from there. If I put the template file in the Office startup folder, it will launch everytime I start Word. Is there a similar way, to package and distribute my code in Excel? I've tried using Add-ins, but I didn't find a way to call the code from the application window.
当我在 Word 中创建了类似的工具时，我可以将所有代码放在一个模板 (.dot) 文件中并从那里调用它。如果我将模板文件放在 Office 启动文件夹中，它会在我每次启动 Word 时启动。有没有类似的方法，在 Excel 中打包和分发我的代码？我试过使用加载项，但我没有找到从应用程序窗口调用代码的方法。
采纳答案by paulmorriss
You can modify the user's personal.xls file, stored in the excel startup directory (varies between Office versions). If you have lots of users though, that can be fiddly.
可以修改用户的personal.xls文件，保存在excel启动目录下（Office版本不同）。但是，如果您有很多用户，那可能会很麻烦。
An alternative way to get over your problem is to store the macro in a template (.xlt) file. Then when the users opens it they can't save it back over the original file, but have to specify a new filename to save it as. The disadvantage of this method is that you then get multiple copies of your original code all over the place with each saved file. If you modify the original .xlt and someone reruns the old macro in a previously-saved .xls file then things can get out of step.
解决问题的另一种方法是将宏存储在模板 (.xlt) 文件中。然后当用户打开它时，他们不能将它保存回原始文件，而是必须指定一个新文件名来保存它。这种方法的缺点是，每个保存的文件都会随处可见原始代码的多个副本。如果您修改原始 .xlt 并且有人在先前保存的 .xls 文件中重新运行旧宏，则事情可能会失控。
回答by saschabeaumont
Simply move your code into an Excel Addin (XLA) - this gets loaded at startup (assuming it's in the %AppData%\Microsoft\Excel\XLSTART folder) but if it's a addin, not a workbook, then only your macros and defined startup functions will be loaded.
只需将您的代码移动到 Excel 插件 (XLA) - 这会在启动时加载（假设它在 %AppData%\Microsoft\Excel\XLSTART 文件夹中）但如果它是插件，而不是工作簿，那么只有您的宏和定义的启动函数将被加载。
If the functions depend on a spreadsheet itself, then you might want to use a combination of templates and addins.
如果函数依赖于电子表格本身，那么您可能希望使用模板和插件的组合。
I'm distributing part of an application like this, we have addins for Word, Excel and Powerpoint (XLA, PPA, DOT) and also Office 2007 'ribbon' versions (DOTM, XLAM and PPAM)
我正在分发这样的应用程序的一部分，我们有 Word、Excel 和 Powerpoint（XLA、PPA、DOT）以及 Office 2007 'ribbon' 版本（DOTM、XLAM 和 PPAM）的插件
The addin startup code creates toolbar buttons if they're not found, this means in any workbook/document/etc they can simply hit the toolbar button to run our code (we have two action buttons and one button that displays a settings dialog)
如果找不到，插件启动代码会创建工具栏按钮，这意味着在任何工作簿/文档/等中，他们只需点击工具栏按钮即可运行我们的代码（我们有两个操作按钮和一个显示设置对话框的按钮）
Templates aren't really the way to go for VBA code, Addins are definitely the way to go...
模板并不是真正适合 VBA 代码的方式，插件绝对是要走的方式......
So to load the toolbars on startup we're using something like.. (checking to see if toolbar exists though - code will run for each worksheet that is opened, but toolbars are persistent for the user session)
因此，要在启动时加载工具栏，我们使用类似……（检查工具栏是否存在 - 代码将为每个打开的工作表运行，但工具栏对于用户会话是持久的）
Public Sub Workbook_Open()
' startup code / add toolbar / load saved settings, etc.
End Sub
hope that helps :)
希望有帮助:)
回答by Dick Kusleika
I always use an Add-in(xla)/Template(xlt) combination. Your add-in creates the menu (or other UI entry points) and loads templates as needed. It also write data that you want to persist to a database (Access, SQLServer, text file, or even an xls file).
我总是使用插件（xla）/模板（xlt）组合。您的加载项创建菜单（或其他 UI 入口点）并根据需要加载模板。它还会将您想要持久保存到数据库（Access、SQLServer、文本文件，甚至是 xls 文件）的数据写入。
The first rule is to keep your code separate from your data. Then, if you later have bug fixes or other code changes, you can send a new add-in and all of their templates and databases aren't affected.
第一条规则是将代码与数据分开。然后，如果您以后有错误修复或其他代码更改，您可以发送一个新的加载项，并且它们的所有模板和数据库都不会受到影响。
回答by Keith Elder
Have you looked into ClickOnce deploying the Excel file?
您是否研究过 ClickOnce 部署 Excel 文件？
回答by Muflix
What about to save an excel to network folder with read only permissions ? The authentication can be done with integrated windows authentication and you don't need to store connection password to the database in the VBA. Then you only need distribute a link to this location to your users only once. If you will do an update, you only change data in that folder without user notice.
将excel保存到具有只读权限的网络文件夹怎么样？身份验证可以通过集成的 Windows 身份验证来完成，您不需要在 VBA 中存储到数据库的连接密码。然后，您只需将指向该位置的链接分发给您的用户一次。如果您要进行更新，您只会更改该文件夹中的数据，而不会通知用户。
