vba 更新 Excel 加载项的过程？

Process for updating Excel add-in?

提问by Lunatik

I have an Excel 2003 add-in deployed in one of our departments that occasionally needs updated. What currently happens is that I publish the new version from my machine to a central location, this updates a version number stored in a database that the add-in checks each time any of the modules within it are run, and if a newer version number is found then the add-in halts with a message informing the user that they need to update the add-in.

我在我们的一个部门中部署了一个 Excel 2003 加载项，偶尔需要更新。当前发生的是，我将新版本从我的机器发布到一个中央位置，这会更新存储在数据库中的版本号，每次运行其中的任何模块时，加载项都会检查该版本号，如果更新的版本号找到然后加载项停止并显示一条消息，通知用户他们需要更新加载项。

This currently involves them running another workbook with a workbook_openevent that changes the add-in.

这当前涉及他们运行另一个工作簿，其中workbook_open包含更改加载项的事件。

What I'd like to have is the add-in detect that a newer version is available and silently install it before carrying on with the user's operation. The problem is that this fails on the first step of uninstalling the existing version because any code in the add-in immediately finishes when the add-in in uninstalled.

我想要的是加载项检测到新版本可用并在继续用户操作之前静默安装它。问题是这在卸载现有版本的第一步失败，因为加载项中的任何代码在卸载加载项时立即完成。

I would create some code in the user's personal.xls on-the-fly to handle the changeover but I am unable to set the 'Trust access to Visual basic project' flag in the users' machines so can't have replicating code in the add-in.

我会在用户的 Personal.xls 中即时创建一些代码来处理转换，但我无法在用户的机器中设置“信任访问 Visual basic 项目”标志，因此不能在加入。

The only way I can see from here is to have two add-ins, one that handles the updates and one that actually does the work. The problem comes when I inevitably have to update the update add-in!

我从这里可以看到的唯一方法是有两个加载项，一个处理更新，一个实际完成工作。当我不可避免地必须更新更新加载项时，问题就来了！

Can anyone else suggest a better solution? This must be 100% Excel, no VSTO etc.

其他人可以提出更好的解决方案吗？这必须是 100% Excel，没有 VSTO 等。

采纳答案by Lunatik

It was nice of Dick Kusleika to blog about managing add-ins today!

很高兴 Dick Kusleika 今天在博客中介绍了管理加载项！

http://www.dailydoseofexcel.com/archives/2009/07/29/conditionally-load-excel-add-ins/

http://www.dailydoseofexcel.com/archives/2009/07/29/conditionally-load-excel-add-ins/

Some good suggestions that can be applied to my situation there.

一些很好的建议可以应用于我在那里的情况。

回答by

We use a separate addin as a bootstrapper.

我们使用单独的插件作为引导程序。

1. The main addin can update the bootstrapper
2. If the main addin needs an update, it loads the bootstapper, then uses an OnTime call to schedule a call to the bootstrap routine in the bootstrap and immediately unloads itself.
3. The ontime call executes.
4. bootstrapper loads main addin, relaunches main addin, unloads itself.

1. 主插件可以更新引导程序
2. 如果主插件需要更新，它会加载引导程序，然后使用 OnTime 调用来安排对引导程序中引导程序例程的调用，并立即卸载自身。
3. 执行 ontime 调用。
4. 引导程序加载主插件，重新启动主插件，卸载自身。

Complicated, but it works.

复杂，但它有效。

回答by Lunatik

I tried something different, step as following: 1. write an addin and put it on shared drive space 2. add the project to VBA references 3. your functions and macros used in excel sheets add as references to addins every time excel sheet loads, new addin and references refreshes.

我尝试了一些不同的方法，步骤如下： 1. 编写一个插件并将其放在共享驱动器空间 2. 将项目添加到 VBA 引用 3. 每次加载 Excel 工作表时，您在 Excel 工作表中使用的函数和宏都添加为对插件的引用，新插件和引用刷新。

i chabge the solution to update button, because of problems when network is not online.

由于网络不在线时出现问题，我找到了更新按钮的解决方案。

回答by Lakshman Prasad

One way I achieved something similar, albeit, a while ago is as follows:

尽管不久前，我实现了类似的一种方法如下：

Provide a button called Update on a common page (may be a common file, if you are using many) and when users click that, it opens a xls in a network location and copies the code from that file to the local file and closes the newly opened files.

在公共页面上提供一个名为 Update 的按钮（可能是一个公共文件，如果您使用多个文件），当用户单击该按钮时，它会在网络位置打开一个 xls 并将代码从该文件复制到本地文件并关闭新打开的文件。

That way, whenever I had an update, I would send an email asking them to update code locally.

这样，每当我有更新时，我都会发送一封电子邮件，要求他们在本地更新代码。

It worked as I had few users and updates were fairly few in number. If you want an auto update, what prevents you from checking the version number during each and every opening of the file.

它工作正常，因为我的用户很少，更新数量也很少。如果您想要自动更新，是什么阻止您在每次打开文件时检查版本号。

回答by geejay

Using out of the box ClickOnce deployment, the add-in version is checked whenever the app using it (Excel) starts. Not sure if you can deploy non-managed code in this way.

使用开箱即用的 ClickOnce 部署，每当使用它的应用程序 (Excel) 启动时，都会检查加载项版本。不确定您是否可以通过这种方式部署非托管代码。

回答by Son Goku ssj4

Because I didn't find the 'workflow' anywhere I'll post my experience from yesterday when I dealt with almost the same problem. (No automatic updates)

因为我没有在任何地方找到“工作流程”，所以我将发布我昨天处理几乎相同问题时的经验。（没有自动更新）

In the @Lunatik's link to dilydoseofexcel.com, Charles posted his Addin (downloadable from http://www.decisionmodels.com/downloads.htm#addload) which resolved my situation.

在@Lunatik 指向 dilydoseofexcel.com 的链接中，Charles 发布了他的插件（可从http://www.decisionmodels.com/downloads.htm#addload下载）解决了我的情况。

Prerequisites

先决条件

Any user that wants to use plugins that are on the remote server has to add the AddLoaderV2.xlaaddin.

任何想要使用远程服务器上的AddLoaderV2.xla插件的用户都必须添加插件。

From Excel: Developer Tab - Excel Add-ins - Browse - ...\AddLoaderV2.xla - select 'NO' when asked to copy it locally

来自 Excel： Developer Tab - Excel Add-ins - Browse - ...\AddLoaderV2.xla - select 'NO' when asked to copy it locally

Structures

结构

• Server side folder structure

• 服务器端文件夹结构

T:\my\remote\folder\SOFTWARE\MSOFFICE\Excel_Add-ins
.
├── AddinLoad.txt
├── AddLoaderV2.xla
└── USER_SonGokussj4
    ├── FinancialFunctions_v0.0.1.xlam
    ├── FinancialFunctions_v0.0.2.xlam
    └── dev_FinancialFunctions_v0.0.3.xlam

• AddinLoad.txt contents

• AddinLoad.txt 内容

FinancialFunctions, auto, T:\my\remote\folder\SOFTWARE\MSOFFICE\Excel_Add-ins\USER_SonGokussj4\

Note: The prefix USERof the folder is just my preference I because maintain addins for a big number of people.

注意：USER文件夹的前缀只是我的偏好，因为我为很多人维护插件。

My Workflow (updating Addin)

我的工作流程（更新插件）

• When I want to update FinancialFunctions_v0.0.2.xlam, I make a copy (because v0.0.2 is used by many people): dev_FinanctialFunction_v0.0.3.xlam
• I open the v0.0.3and make needed changes
• When I'm done, I rename it to FinanctialFunctions_v0.0.3.xlam
• I send an e-mail to my department, that new version is available
• Now every new opened Excel will autoload (defined within AddinLoad.txt) the v0.0.3
• If user don't want to close their Excel, in AddinsTab, there is: https://i.imgur.com/H2ru2hV.pngso the user just select the Addin to reload

• 当我想更新时FinancialFunctions_v0.0.2.xlam，我复制一份（因为v0.0.2被很多人使用）：dev_FinanctialFunction_v0.0.3.xlam
• 我打开v0.0.3并进行所需的更改
• 完成后，我将其重命名为 FinanctialFunctions_v0.0.3.xlam
• 我给我的部门发送了一封电子邮件，新版本可用
• 现在每个新打开的 Excel 都将auto加载（在 内定义AddinLoad.txt）v0.0.3
• 如果用户不想关闭他们的 Excel，在AddinsTab 中，有：https: //i.imgur.com/H2ru2hV.png所以用户只需选择 Addin 即可重新加载

Closure

关闭

I imagine it won't be a lot of work to do some automatization from yourAddin like "check if new version is available, if yes, call the reloadfunction of the AddLoaderV2Addin."

我想从你的插件中做一些自动化不会有很多工作，比如“检查新版本是否可用，如果是，调用插件的reload功能AddLoaderV2。”

Hope this helps someone. My biggest problem was understanding the naming of the .xlamfiles and AddinLoad.txtroot name to always load only the newest version. There is a 3-page readme when you download his Addin.

希望这可以帮助某人。我最大的问题是理解.xlam文件的命名和AddinLoad.txt根名称以始终只加载最新版本。当您下载他的 Addin 时，有一个 3 页的自述文件。

Charles to the rescue! Have a great day.

查尔斯来救援！祝你有美好的一天。

Now I have to find something similar to load PowerPoint addins...

现在我必须找到类似的东西来加载 PowerPoint 插件...