App-V: On Virtualizing Multiple Excel Add-ins

Yes, I’m still obsessed with the subject of add-in virtualization. I felt it also necessary to ensure that there was a discussion of add-in types and multiple Office add-ins (particularly Excel) before I finally leave this topic of discussion. Have you ever noticed that when you are managing\loading add-ins in Excel that you have multiple distinct types of add-ins. The two most common types are COM add-ins (common format for 3rd-party applications) and Excel Add-ins or what we refer to technically as Automation Add-ins (VSTO, XLAM add-ins.)

 

COM Add-Ins

COM add-ins act as in-process COM servers (like an ActiveX DLL) that is built off the IDTExtensibility interface. These are pretty much event-driven and present themselves to the user in the form of custom menus, commands, etc. When a COM Add-in is installed on a user's system, registry entries are created for the Add-in. In addition to normal COM registration, a COM Add-in is registered for each Office application in which it runs. COM Add-ins used by Excel are registered in the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\

For example, if I have an Add-in called “Data Transfer Excel Add-in.” it would register in a key similar to the image below:

 

NOTE: Do not get confused. This registration may be used with the other add-in registrations that Office applications may use (in the HKLM or the HKCU\Software\Microsoft\Office\<VERSION>\<App>\Addins key.) That can also be a source of troubleshooting sometimes.

Dynamic Configuration is important when leveraging an add-in when it comes to COM settings. If the Add-in will be packaged with the application, it should remain isolated – which is the default. If the add-in is virtualized but Office is locally installed, then the COM add-in must have its COM mode configured as “Integrated” with in-process registration. If you are linking the add-ins with a virtual instance of Office via a connection group, this is also recommended (using the element “<COM Mode="Integrated">”)

NOTE: LOCAL_INTERACTION_ENABLED set to TRUE in the 4.6 OSD file achieves this same result.

Automation Add-Ins

Automation Add-ins build on COM Add-ins in that functions in Automation Add-ins can be called from formulas in Excel worksheets. While COM Add-ins must be in-process COM servers that support the IDTExtensibility2 interface; however, Automation Add-ins can be in-process or out-of-process COM servers and implementation of IDTExtensibility2 is optional. Understanding what type of COM server will determine how the add-ins COM configuration may need to be configured in the applications dynamic configuration file.

Order of Add-Ins

When you make additions to the list in the Add-Ins dialog box or when you select and clear Add-ins in the list, Excel stores your changes in the registry. First, Excel uses the following registry setting to determine whether or not an Automation Add-in in the Add-in list is loaded:

Key: HKEY_CURRENT_USER\Software\Microsoft\Office\<VERSION>\Excel\Options

String OPENx (where x is the numerical order.)

Sample Value: /A “ServerName.Classname”

 

Note: The /A switch denotes it is loading an automation add-in *AND* unlike COM Add-ins, automation add-ins are loaded on demand so the LoadBehavior registry key is not necessary for these types of add-ins.

 

 

When an Automation Add-in that is listed in the Add-Ins dialog box is cleared, a subkey with a name equal to the Add-in's Program ID is created in the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\<VERSION>\Excel\Add-in Manager

This registry setting ensures that Automation Add-ins that you have added to the Add-ins list are retained in the list even when you have chosen not to load them. Both the Add-in Manager and OPENx registry settings need to be managed properly when virtualizing add-ins.

Caveats when Virtualizing Multiple Add-ins with App-V

When Excel loads these automation add-ins it will expect to see a ordinal series of OPEN entries in the registry (OPEN, OPEN1, OPEN2, OPEN3, etc.) If it is the first add-in to be installed, the registry value created will be “OPEN.” When the second add-in is installed, it will register “OPEN1.” The third add-in installed will then register “OPEN2” and . . . well, you get the idea.

So here is the problem that often arises: Let’s say you are virtualizing three Excel Add-ins separately and you want to link them with a virtualized Office package (or even linking local Office by pulling into an empty package and linking that with these three add-ins.) Chances are the first time you do this, you will fail – as the case with many of us.

 

 

 

If I sequence all of these add-ins separately and link them all with Office through a connection group, I have the following factors to consider with regards to these overlapping OPEN values:

  • Registry opacity within the add-in package
  • Resultant registry opacity upon Connection Group deployment

During sequencing, the normal behavior to determine default registry opacity goes as follows:

 

 

This of course, can be adjusted using the virtual registry tab within the sequencer. If you virtualize each add-in separately (which is normal) and add the add-ins into Excel with each sequence, you will find that each one appears as an OPEN registration. When you combine the add-ins together, you will likely find only one of the add-ins working upon first launch.

 

 

 

Another problem to avoid but one that is less likely to occur is to ensure that your OPEN registrations are in a direct sequence (OPEN, OPEN1, OPEN2, etc.) They have to be consecutive. If you have OPEN, OPEN3, OPEN5, etc. configured then you find Excel stops loading after the first one because OPEN2 is missing.

What I am Currently Doing

I take advantage of the knowledge of knowing that when you use Connection Groups, the number one entry in <appv:packages> section of the Connection Group XML descriptor document takes precedence. So if I were to employ a connection group that contained a local instance of Office, I would simply import a custom REG file containing the OPEN registrations in the correct order into an empty package (during sequencing) that also contains the shortcut extension points to the local Office applications. I then ensure that the empty package is at the top of the order within the Connection Group.

<appv:Packages>

  <appv:Package DisplayName=”Local Office” PackageId=”<GUID>” VersionID=”<GUID>”/>

  <appv:Package DisplayName=”Add-in #1” PackageId=”<GUID>” VersionID=”<GUID>”/>

  <appv:Package DisplayName=”Add-in #2” PackageId=”<GUID>” VersionID=”<GUID>”/>

  <appv:Package DisplayName=”Add-in #3” PackageId=”<GUID>” VersionID=”<GUID>”/>

</appv:Packages>

 

 

You have to ensure that the resultant virtual registry used by the parent Excel application has a correct OPEN sequence of registrations. You also have to ensure that the opacity will not conflict with any local registrations. Keeping these things in mind, I have the following recommendations when I am devising a add-in strategy for my customers.

Virtualize NO Excel automation add-ins.

-or-

Virtualize ALL Excel automation add-ins. Use Connection Groups to bridge a local or virtual Excel instance or package everything together if necessary,