Excel: require Power Query add-in load from VBA macro

Question

The scenario is a Windows Server 2012 R2, 64 bit; Excel 2010, 32 bit. Many users, just a couple of them have administrative privileges. I installed Power Query from the built-in administrator. Without asking anything, the add-in got installed for all users; I mean that its settings may be found in a registry key under HKLM, not HKCU.

The key is

HKEY_LOCAL_MACHINE -> SOFTWARE -> Wow6432Node -> Microsoft -> Office -> Excel -> AddIns -> Microsoft.Mashup.Client.Excel

and the relevant value is

LoadBehavior (REG_DWORD)

Now just a few users really need Power Query. I don't want it to load for everybody, everytime Excel is launched. I tried some different settings for the LoadBehavior value (see this link). I found the following:

• If LoadBehavior = 2, the add-in is not loaded for any user, no way.
• If LoadBehavior = 3, the add-in is loaded for all users. However, if I create a specific key for a specific user (in the registry), the user can disable the add-in from Excel. E.g., in HKEY_CURRENT_USER, the path for the key is:

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

• More specifically, it is sufficient to put in the user's key the LoadBehavior value, and this allows the user to decide whether he wants the add-in loaded or not. A value of 3 means "Load", a value of 2 means "No load".
• I need exactly the opposite: add-in normally not loaded, and some users allowed to load it. I found that it is possible to set the LoadBehavior in the HKLM to 9. This means "Load on demand". I.e., the add-in is only loaded when the user requires an action depending on the add-in itself. Quite fine for me.
• At this point I may leave out the key for specific users (in HKCU, e.g.). However, I found out that if I create it, it takes precedence over the HKLM (when the latter has LoadBehavior = 9). So it is possible to decide, for specific users, to have the add-in on "Always Load" mode. Just set LoadBehavior = 3 in the key under HKCU.

All this seems fine. Now the problem is that I need to call some Power Query actions from a VBA procedure. If Power Query is already loaded, all is fine. But if it is not loaded, even with the "Load on demand" setting, the action fails. In order to get Power Query loaded, one has to press some button on the Excel GUI which calls a Power Query action.

I found that there is a property of the add-in object available in VBA which indicates whether the add-in is loaded, and may be set to load or unload it from VBA. It is:

Application.COMAddIns.Item("Microsoft.Mashup.Client.Excel").Connect

If it is True, the add-in is loaded, and if it is False, the add-in is unloaded.

Now it should be possible to load the add-in just by setting this property to True. However, this is not the case in my scenario: the result is an error (80004005). This seems a problem related to the user not having administrative privileges. See this page - this behavior is considered a bug.

My last idea, which I will try later, is to completely remove the LoadBehavior in the key under HKLM. I already checked out that this prevents users from seeing the add-in, unless the user-specific key is created, in which case the user can set the add-in load behavior autonomally. I will see what happens in this case when the load is requested from VBA.

Meanwhile, I'd appreciate any idea to solve the thing: having Power Query not loaded normally, possibly available on demand for all users, loaded automatically from VBA (at least for some users), and all this without having to manually add the user-specific key for all users. It is acceptable to add this key for a few users, those who actually need Power Query.

EDIT

Removing, or renaming, the LoadBehavior value in the key under HKLM works. Power Query is then only seen by users who have a specific key under HKCU. If, in this key, the value of LoadBehavior is set to 3 (or 2), then the add-in is loaded by default (respectively, not loaded). The VBA instruction to change the .Connect property works fine; it switches the LoadBehavior between 3 (True) and 2 (False). Luckily, I can also set LoadBehavior = 9 in the registry (under HKCU), and the .Connect property is still writable. In this situation, when this property is assigned a True value the add-in is loaded, but the LoadBehavior value stands still at 9, so that upon closing and re-opening Excel the add-in is unloaded, is set as "Load on demand", and may be loaded from VBA.

This is exactly the behavior I was looking for; the only caveat is that the key needs to be created for all users who need Power Query. Since in my situation they may be counted on one hand's fingers, this solution is acceptable.

I'm still curious to see whether anyone comes out with any better solution.

Answer 1

Have you considered using a powershell script to update the registry entries for each user accordingly? The following example is a bit complex, line 111 has the loop you need to set it for many users.

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

My other recommendation is you should not use VBA in Excel 2010 to call anything PowerQuery related. Given how it is an add-in I don't think it's included in the service agreements for Excel 2010. VBA interaction with PowerQuery was not fully supported in Excel 2010 or debugged. I noticed in several patches broke the GUI in PowerQuery altogether. Upgrading to Excel 2013 or 2016 was the fix.

When using Excel 2016 VBA editor to interact with PowerQuery, the macro code is challenging to debug and get working, and its references are poorly documented. It's more stable to show you an error and not just crash but it's very challenging to troubleshoot the errors the compilers find.