Excel:需要从 VBA 宏加载 Power Query 加载项

Question

场景是 Windows Server 2012 R2，64 位； Excel 2010，32 位。许多用户，其中只有几个拥有管理权限。我从内置管理员安装了 Power Query。无需询问，就为所有用户安装了插件；我的意思是它的设置可以在 HKLM 下的注册表项中找到，而不是 HKCU。

关键是
HKEY_LOCAL_MACHINE -> SOFTWARE -> Wow6432Node -> Microsoft -> Office -> Excel -> AddIns -> Microsoft.Mashup.Client.Excel
相关值是
LoadBehavior (REG_DWORD)
现在只有少数用户真正需要 Power Query。我不希望它为每个人加载，每次启动 Excel 时。我为 LoadBehavior 值尝试了一些不同的设置(参见 this 链接)。我发现了以下内容:

如果 LoadBehavior = 2，则不会为任何用户加载加载项，不可能。

如果 LoadBehavior = 3，则为所有用户加载加载项。但是，如果我为特定用户(在注册表中)创建特定键，则用户可以从 Excel 禁用加载项。例如，在 HKEY_CURRENT_USER 中， key 的路径是:

Software -> Microsoft -> Office -> Excel -> Addins -> -Microsoft.Mashup.Client.Excel

更具体地说，将 LoadBehavior 值放入用户的键就足够了，这允许用户决定是否要加载加载项。值为 3 表示“负载”，值为 2 表示“无负载”。

我需要的恰恰相反:加载项通常未加载，并且某些用户允许加载它。我发现可以将 HKLM 中的 LoadBehavior 设置为 9。这意味着“按需加载”。即，仅当用户需要依赖于加载项本身的操作时才加载加载项。对我来说很好。

在这一点上，我可能会忽略特定用户的 key (例如在 HKCU 中)。但是，我发现如果我创建它，它优先于 HKLM(当后者的 LoadBehavior = 9 时)。因此，对于特定用户，可以决定让加载项处于“始终加载”模式。只需在 HKCU 下的键中设置 LoadBehavior = 3。

这一切似乎都很好。现在的问题是我需要从 VBA 过程调用一些 Power Query 操作。如果 Power Query 已加载，则一切正常。但如果没有加载，即使设置了“按需加载”，操作也会失败。为了加载 Power Query，必须按下 Excel GUI 上的某个按钮来调用 Power Query 操作。

我发现 VBA 中有一个可用的加载项对象属性，它指示加载项是否已加载，并且可以设置为从 VBA 加载或卸载它。这是:
Application.COMAddIns.Item("Microsoft.Mashup.Client.Excel").Connect
如果是 True ，加载项已加载，如果是 False ，加载项已卸载。

现在应该可以通过将此属性设置为 True 来加载加载项。但是，在我的场景中情况并非如此:结果是错误 (80004005)。这似乎是与没有管理权限的用户有关的问题。见 this页面 - 此行为被视为错误。

我的最后一个想法，我稍后会尝试，是完全删除 HKLM 下 key 中的 LoadBehavior。我已经检查出这会阻止用户看到加载项，除非创建了用户特定的 key ，在这种情况下，用户可以自动设置加载项加载行为。当从 VBA 请求加载时，我将看到在这种情况下会发生什么。

同时，我很感激解决这个问题的任何想法:让 Power Query 未正常加载，可能按需为所有用户提供，从 VBA 自动加载(至少对于某些用户)，所有这些都无需手动添加用户- 所有用户的特定 key 。为少数用户添加此键是可以接受的，这些用户实际上需要 Power Query。

编辑

删除或重命名 HKLM 下项中的 LoadBehavior 值。只有在 HKCU 下拥有特定 key 的用户才能看到 Power Query。如果在此键中将 LoadBehavior 的值设置为 3(或 2)，则加载项默认加载(分别为未加载)。更改 .Connect 属性的 VBA 指令工作正常；它在 3(真)和 2(假)之间切换 LoadBehavior。幸运的是，我还可以在注册表中(在 HKCU 下)设置 LoadBehavior = 9，并且 .Connect 属性仍然是可写的。在这种情况下，当此属性被分配一个 True 值时，加载项被加载，但 LoadBehavior 值仍为 9，因此在关闭和重新打开 Excel 时加载项被卸载，设置为“加载需求”，并且可以从 VBA 加载。

这正是我正在寻找的行为；唯一需要注意的是，需要为所有需要 Power Query 的用户创建 key 。因为在我的情况下，它们可能是用一只手的手指来数的，所以这个解决方案是可以接受的。

我仍然很想知道是否有人提出了更好的解决方案。

Answer 1

您是否考虑过使用 powershell 脚本来相应地更新每个用户的注册表项？下面的例子有点复杂，第111行有你需要为很多用户设置的循环。

https://daniel.streefkerkonline.com/2014/04/09/re-enable-microsoft-office-add-ins-with-powershell/

我的另一个建议是您不应在 Excel 2010 中使用 VBA 来调用任何与 PowerQuery 相关的内容。鉴于它是一个加载项，我认为它没有包含在 Excel 2010 的服务协议(protocol)中。Excel 2010 中不完全支持 VBA 与 PowerQuery 的交互或调试。我注意到有几个补丁完全破坏了 PowerQuery 中的 GUI。升级到 Excel 2013 或 2016 是解决方法。

使用 Excel 2016 VBA 编辑器与 PowerQuery 进行交互时，宏代码很难调试和运行，而且其引用记录不完整。向您显示错误更稳定，而不仅仅是崩溃，但对编译器发现的错误进行故障排除非常具有挑战性。