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的数据库报告工具。 Currentely，所有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中打包和分发我的代码？我尝试过使用加载项，但是我找不到从应用程序窗口调用代码的方法。
5 个解决方案
#1
2
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.
您可以修改存储在Excel启动目录中的用户的personal.xls文件（因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文件中的旧宏，那么事情就会失败。
#2
10
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 Addin（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'功能区'版本（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)
如果找不到它们，则addin启动代码会创建工具栏按钮，这意味着在任何工作簿/文档/等中，他们只需按工具栏按钮即可运行我们的代码（我们有两个操作按钮和一个显示设置对话框的按钮）
Templates aren't really the way to go for VBA code, Addins are definitely the way to go...
模板并不是真正的VBA代码，Addins肯定是要走的路......
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 :)
希望有帮助:)
#3
2
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.
第一条规则是将代码与数据分开。然后，如果您以后修复了错误或更改了其他代码，则可以发送新的加载项，并且不会影响其所有模板和数据库。
#4
0
Have you looked into ClickOnce deploying the Excel file?
您是否研究过部署Excel文件的ClickOnce？
#5
0
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中的数据库。然后，您只需向用户分发一次指向此位置的链接。如果要进行更新，则只更改该文件夹中的数据，恕不另行通知。
#1
2
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.
您可以修改存储在Excel启动目录中的用户的personal.xls文件（因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文件中的旧宏，那么事情就会失败。
#2
10
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 Addin（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'功能区'版本（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)
如果找不到它们，则addin启动代码会创建工具栏按钮，这意味着在任何工作簿/文档/等中，他们只需按工具栏按钮即可运行我们的代码（我们有两个操作按钮和一个显示设置对话框的按钮）
Templates aren't really the way to go for VBA code, Addins are definitely the way to go...
模板并不是真正的VBA代码，Addins肯定是要走的路......
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 :)
希望有帮助:)
#3
2
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.
第一条规则是将代码与数据分开。然后，如果您以后修复了错误或更改了其他代码，则可以发送新的加载项，并且不会影响其所有模板和数据库。
#4
0
Have you looked into ClickOnce deploying the Excel file?
您是否研究过部署Excel文件的ClickOnce？
#5
0
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中的数据库。然后，您只需向用户分发一次指向此位置的链接。如果要进行更新，则只更改该文件夹中的数据，恕不另行通知。