在多个工作簿中更新VBA [英] Update VBAs in multiple workbooks

问题描述

结束游戏:用户保存的工作簿将打开，并从目标文件中镜像代码.

END GAME: A user saved Workbook opens and mirrors code from a target file.

我正在尝试创建一个具有Excel前端和Access后端的简单VBA应用程序.将有多个用户可以选择将Excel前端文件保存在所需的任何位置.

I am trying to create a simple VBA application that has an Excel front-end and an Access back-end. There will be multiple users who would have the option to save the front-end Excel piece anywhere they desire.

我想知道在需要推送更新时能够在所有用户实例中更新宏的最有效方法.

I would like to know the most efficient way to be able to update macros in all user instances when I need to push updates.

基本上，我想从 Workbook_open 上的全局"文件中镜像代码.过去，我确实设置了代码来打开单独的工作簿并运行代码( dim x as workbook ， open ， app.runmacro 等)，但是我认为这并不是最有效的方法.

Essentially, I would like to mirror code from a "global" file on Workbook_open. In the past I did actually set code to open a separate workbook and run code (dim x as workbook, open, app.runmacro and etc.), But I think that is not really the most efficient way to do it.

推荐答案

为此想到了四个可能的解决方案(除了您选择使用中介工作簿的选择之外)，还有其他可能的解决方案:

Four possible solutions pop to mind for this (other than your option of having an intermediary workbook), there are likely others:

1. 将工作簿视为一个纯粹的界面，然后将代码移至访问数据库，并使其接受工作簿作为参数，如果需要.好处是可以将代码保存在一个文件中位置(访问)，但它有两个主要缺点.每个用户需要安装Access才能实例化调用方法的应用程序，它将锁定您的接口"-即，更改其调用Access宏的方式仍然需要更新工作簿.
2. 创建规范工作簿并检查用户工作簿版本打开时，将其与规范的工作簿相对应.如果版本是不同，打开新的，将所有数据移到其中，删除旧文件，然后将新副本保存到与旧文件相同的文件名.这种方法的主要缺点是要确保不运行旧代码可能很困难，因为您将需要采取措施防止用户可能中止的情况更新过程，仍然有旧代码的工作副本.
3. 自动执行VBE(有关实现的详细信息，请参见此答案-有关如何执行此操作的资源很多).取决于如何您想要执行此操作，则可以将当前模块存储为文件并导入它们，或将代码存储在数据库本身中，以及查询它.这种方法的主要缺点是VBE对于更改实际正在运行的代码可能会变幻无常.我是不知道我会相信它会改变它自己的实现.你在每个用户的安全性中，还需要允许访问VBE设置，可能会带来安全威胁.
4. 将工作簿本身的位置存储在数据库中，然后用外部代码推送更新的副本.该工作簿将报告打开时的文件路径，如果尚未记录在数据库中，检查它是否为最新版本，然后为自己写一条记录.这样做的缺点是只能通知用户他们没有最新消息版本(例如，如果他们在Explorer中移动工作簿，并且推后才打开它.

1. Treat the Workbook as purely an interface, and move the code to the Access database and have it accept the Workbook as a parameter if needed. The advantage would be the code could be maintained in one place (Access), but it would have two main disadvantages. Each user would need to have Access installed in order for it to instantiate the application to call methods on, and it would lock in your "interface" - that is, changes to how it calls Access macros would still require Workbook updates.
2. Create a canonical Workbook and have the user Workbook version check against the canonical Workbook when opened. If the version is different, open the new one, move all of the data to it, delete the old one, and save the new copy to the same filename as the old one. The main disadvantage of this method would be ensuring that old code doesn't run might be difficult, as you would need to take measures to prevent situations where the user could abort the update process and still have a working copy of the old code.
3. Automate the VBE (see this answer for implementation details - there are numerous resources on how to do this). Depending on how you wanted to do this, you could either store the current modules as files and import them, or store the code in the database itself and query for it. The main disadvantages of this method are that the VBE can be fickle about changing code that is actually running. I'm not sure that I'd trust it to change it's own implementation. You would also need to allow access to the VBE in each user's security settings, which may pose a security threat.
4. Store the location of the Workbooks themselves in the database, then push out updated copies with external code. The Workbook would report it's filepath when opened, and if it wasn't already recorded in the database, check to see if it was the most current version, and then write a record for itself. This has the disadvantage of only being able to inform the user that they don't have the current version if they (for example) move the Workbook in Explorer and don't open it until after your push.

请注意，除了最后一个解决方案外，这些都是拉"型解决方案，而不是推"型解决方案.不管您使用哪种版本检查方法，任何推送解决方案都将具有数字4的缺点-没有可靠的方法来确保推送捕获所有无效版本.

Note that these are all "pull" type as opposed to "push" type solutions with the exception of the last one. Regardless of the method you use for version checking, any push solution is going to share the disadvantage of number 4 - there is no reliable way to make sure that a push catches all the invalidated versions.

这篇关于在多个工作簿中更新VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！