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 managingloading 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_USERSoftwareMicrosoftOfficeExcelAddins
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 HKCUSoftwareMicrosoftOffice<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_USERSoftwareMicrosoftOffice<VERSION>ExcelOptions
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_USERSoftwareMicrosoft<VERSION>ExcelAdd-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,
madvirtualizer
The Mad Virtualizer at Microsoft 
1.) That is correct. 2.) What you may be resorted to do is silo your add-ins by machines or perhaps bake in an office image and use an empty package within the user targeted connection group to contain the office applications (brought in by shortcuts.)
What if user one has addin1 and addin2, and user two should have addin1,addin2 and addin3, and user3 addin1 and addin3.
How do you make the addin loading dynamic. Im currently looking how to solve this in a shared desktop environment where we have a few office addins and want to virtualize them. But if the OPEN registry key needs to be sequencial this might be a problem and installation of the addins the only option.
Any ideas?
Correct. Right now, Office should not be targeted to users – BUT – you could target connection groups to users where each one has an empty connection group containing shortcuts to the local Office deployment. You can then link the user-targeted virtual add-ins and registry configurations through those user-targeted connection groups.
Hi Steve…great post. We are currently implementing app-v 5 into a citrix 6.5 rds env. We have many 1000's of users and many addin's where users would need a combination of addin's. I have concerns with multiple connections groups, setting priorities etc. Ideally our engineering team would like office 2010 and all addin's virtual. Again, I am just not sure this is going to work in our environment. Have you any thoughts / recommendations to simplfy this and reduce connection group management. Should we really be looking to go physical office with virtual addins?
Here's the challenge: If you virtualize Office 2010, then you have to publish it globally. If you target all of the add-ins to individual users (with Office 2010 locally installed) you may find better results if user-targeting is your goal and you can avoid CG's. Do you know the breakdown of Add-in types and Office application mappings?
Hi Steve..apologies for the delay. The remit is for office 2010 to be virtualized in the shared CTX environement. Office has been publsihed globally with com mode intergrated set to true. Now, the main issues we find is 1. Office can only exist in a single connection group and all apps in the connection must match office integrated mode. 2. This means any applications that have addin’s (and we have a lots) will all be available for all users. Some of these are licensed addin’s. Some of these apps could be small addin’s or even vendor apps such as mindmanager. Additionally, we will be running published shortcuts so on launch if a user has many apps in a connection group this will dramatically slow down launch time. There is a real mixture of COM, open keys for excel, outlook, word, powerpoint etc. I appreciate this is quite complex scenario. Thanks
I constantly stress to my customers that significant assessment and planning revolving around the design
I constantly stress to my customers that significant assessment and planning revolving around the design
Hi Steve…very informative post. I have a specific question related to VSTO addin. We are currently having issue with sequencing VSTO addin in App-v 5.1. I think issue is that VSTO addin because of shadow copy is trying to copy dll, exe to virtual environment while loading and CoW is preventing it from doing so. It is working fine with App-v 4.6
Any ideas on how to sequence VSTO addins?
Based on this blog, I have have sequenced MS Office 2013 and 2 packages with excel automation addin.
I have removed the open registrations from both the addin packages and created a 3rd package with only the open registrations of the addin packages.
Now I have created a connection group xml, with the order as follows:
1. MS Office Virtualised (Pkg ID & Ver ID).
2. Empty Package containing the open registrations of both addins (Pkg ID & Ver ID).
3. First Addin package (Pkg ID & Ver ID).
4. Second Addin package (Pkg ID & Ver ID).
When I enable the connection group with the above xml file, I am only able to see the open registration of the First Addin package under virtual registry. Also, upon launching the excel shortcut, the first addin shows active and the second inactive.
Please suggest.
Hi Steve, thanks for this post. I am currently in process of virtualising 3 excel addin packages to be used along side with office click 2 run as well as volume licensed copy . I have also created a base office package and linked its packagegui_versionguid under run virtual key.
then trying to use connection group for all other 3 excel addin package.
my question is around below line :
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
Do I need to create dummy open, open1 and open 2 keys in my base office package ? If you can guide me here will be great help.