Jaa

App-V: On Virtualizing Multiple Excel Add-ins

  • Artikkeli

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,

Comments

  • Anonymous
    January 01, 2003
    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.)

  • Anonymous
    October 21, 2013
    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?

  • Anonymous
    October 22, 2013
    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.

  • Anonymous
    December 02, 2013
    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?

  • Anonymous
    December 03, 2013
    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?

  • Anonymous
    January 28, 2014
    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

  • Anonymous
    February 21, 2014
    I constantly stress to my customers that significant assessment and planning revolving around the design

  • Anonymous
    May 29, 2014
    I constantly stress to my customers that significant assessment and planning revolving around the design

  • Anonymous
    June 15, 2016
    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.6Any ideas on how to sequence VSTO addins?

  • Anonymous
    August 25, 2016
    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.

  • Anonymous
    September 05, 2016
    The comment has been removed

  • Anonymous
    December 28, 2016
    We seem to have found an issue with the most recent versions of Office 2013 C2R packages that are converted to App-V, it seems that putting this into ANY CG (even a completely empty one), prevents Word from starting.Any ideas?

  • Anonymous
    April 06, 2017
    Is it possible/supported for App-V 5.1 virtualized office add-in to work with Office 365 click-to-run non-App-V version?

    • Anonymous
      April 26, 2017
      I read in the comments in http://packageology.com/2016/08/office-365-app-v/ that it appears MS are supporting App-V 5.1 virtualized office add-in to work with Office 365. I've not successfully sequenced one of my 2013 add-ins for 365 yet but I’m making progress using the suggestion from the article client 5.1 HF 8 and the reg key below. I'm looking at different COM \ object settings available in the manifest.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ClickToRun\OverRidekeyname=AllowJitvInAppvVirtualizedProcess (reg_dword)keyvalue=1