App-V 5 with Excel Automation Addins and RunVirtual

This blog entry discusses how we can use App-V 5, Connection Groups and RunVirtual to present Excel automation addins to end users.

Microsoft Excel addins come in two forms – either an automation addin or a Component Object Model (COM) addin.

From an App-V perspective, capturing a COM addin is a relatively trivial process since they are registered using a static registry value – namely a ProgId in the following registry location:

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

Automation addins however, work in a different way. When they are registered in the Windows registry side-by-side with other automation addins, they create a dynamically enumerated OPEN{x} key in the following registry location:

HKEY_CURRENT_USER\Software\Microsoft\Office\{Version}\Excel\Options

For example:

OPEN      C:\alkane\addin1.xla
OPEN1     C:\alkane\addin2.xla
OPEN2     C:\alkane\addin3.xla

This obviously creates a bit of a headache when capturing an automation addin with any packaging toolset. Put simply, if we captured automation addin 1 on a clean virtual machine it would register under the following registry value:

HKEY_CURRENT_USER\Software\Microsoft\Office\{Version}\Excel\Options\OPEN

and if we captured addin 2 on a clean virtual machine it would also register under the same registry value:

HKEY_CURRENT_USER\Software\Microsoft\Office\{Version}\Excel\Options\OPEN

So if they were both installed (for thick installations) or streamed (App-V in a connection group) to the same machine, each package would conflict and you would only see the ‘last’ addin.

From an App-V perspective, this isn’t too bad if you are using the ugly ‘App-V 4’ method of providing Excel addins by isolating them as separate packages; by this, I mean creating package 1 with a shortcut called “Excel with Addin 1” and package 2 with a shortcut called “Excel with Addin 2” (having said that, you may have issues seeing native Excel addins at the same time). But users don’t like this clunky approach. They expect to launch their local instance of Excel and see all of the required addins side by side. And to achieve this optimal user experience you would need to use RunVirtual to present your Excel addins with connection groups.

I should note too, that removing automation addins isn’t trivial either, since the OPEN{x}registry values must stay in sequential order. If we installed 3 addins:

OPEN      C:\alkane\addin1.xla
OPEN1     C:\alkane\addin2.xla
OPEN2     C:\alkane\addin3.xla

and then removed addin2.xla so it became this:

OPEN      C:\alkane\addin1.xla
OPEN2     C:\alkane\addin3.xla

It would break things because OPEN1 is missing. Instead it would need refactoring to:

OPEN      C:\alkane\addin1.xla
OPEN1     C:\alkane\addin3.xla

Luckily, rather than scripting this logic the Excel automation object (Excel.Application) does all this for us. And we can dynamically configure our Excel addins using PowerShell. A few things to note:

  • Before we create an instance of Excel.Application, we disable RunVirtual. Why? Because instantiating Excel.Application spawns an Excel.exe process, which in turn kicks in RunVirtual and any associated packages! If you’re using my aforementioned approach to present Excel addins using RunVirtual this could create a world of pain where ultimately Excel.exe gets so confused that it times out! Of course, we re-enable RunVirtual at the end.
  • It creates a log file in the %temp% folder so you can see what’s happening. Rename the log file as required on line 1.
  • You will need to save this script as ‘addins.ps1’ and lump it in the Scripts folder inside your App-V package.
$logfile = "$($env:temp)\your_log_name.log"
function Write-Log {
Param($message)
$datetime = Get-Date -Format "dd/MM/yyyy HH:mm:ss"
Add-Content $logfile "$($datetime): $message"
}
function Disable-Excel-Runvirtual {
if (Test-Path HKCU:\SOFTWARE\Microsoft\AppV\Client\RunVirtual\Excel.exe) {
Write-Log ('Disabling RunVirtual for Excel.exe (if configured)')
Rename-Item HKCU:\SOFTWARE\Microsoft\AppV\Client\RunVirtual\Excel.exe -NewName Excel.exe.disable
}
}
function Enable-Excel-Runvirtual {
if (Test-Path HKCU:\SOFTWARE\Microsoft\AppV\Client\RunVirtual\Excel.exe.disable) {
Write-Log ('Enabling RunVirtual for Excel.exe (if configured)')
Rename-Item HKCU:\SOFTWARE\Microsoft\AppV\Client\RunVirtual\Excel.exe.disable -NewName Excel.exe
}
}
function Get-Current-Script-Directory {
$currentDirectory = [System.AppDomain]::CurrentDomain.BaseDirectory.TrimEnd('\') 
if ($currentDirectory -eq $PSHOME.TrimEnd('\')) 
{     
$currentDirectory = $PSScriptRoot 
}
Write-Log ('Current script directory is: ' + $currentDirectory)
return $currentDirectory
}
function Delete-AddInRegistry {
Param(  
[string]$AppVCurrentUserSID,
[string]$ExcelVersion,
[string]$AppVAddinPath,
[string]$AppVPackageId,
[string]$AppVVersionId
)  
#when an addin is uninstalled, it automatically creates a registry entry in the 'add-in manager' key.  We must delete it.
#remove registry for this package if exists
$registrykey = "HKCU:\Software\Microsoft\Office\$ExcelVersion\Excel\Add-in Manager"
Write-Log ("Deleting registry for this package (if exists): " + $registrykey + " " + $AppVAddinPath)
Remove-ItemProperty -path $registrykey -name $AppVAddinPath -Force -ErrorAction SilentlyContinue       
#Also ensure registry for the addin itself is removed
$registrykey = "HKCU:\Software\Microsoft\Office\14.0\Excel\Options"
$RegKey = (Get-ItemProperty $registrykey)
$RegKey.PSObject.Properties | ForEach-Object {
If($_.Value -like "*$AppVAddinPath*"){
Write-Log ("Deleting registry for this package: " + $registrykey + " " + $_.Name)
Remove-ItemProperty -path $registrykey -name $_.Name -Force -ErrorAction SilentlyContinue  
}
}       
}
function Install-Addin()
{
Param(
[String]$AppVAddinPath
)
$ExitCode = 1
$AppVPackageId = ""
$AppVVersionId = ""
$ExcelVersion = ""
$AppVCurrentUserSID = ([System.Security.Principal.WindowsIdentity]::GetCurrent()).User.Value	
Write-Log ('Installing: ' + $AppVAddinPath)
#If RunVirtual is configured for Excel.exe it may cause issues with COM automation, so we disable it and re-enable it later
Disable-Excel-Runvirtual
$CurrentScriptDirectory = Get-Current-Script-Directory
if (Test-Path $CurrentScriptDirectory) {
$AppVPackageId = (get-item $CurrentScriptDirectory).parent.parent
$AppVVersionId = (get-item $CurrentScriptDirectory).parent
Write-Log ('Package ID is: ' + $AppVPackageId)
Write-Log ('Version ID is: ' + $AppVVersionId)
} 
if (Test-Path -Path $AppVAddinPath -PathType Leaf) {
$Addin = Get-ChildItem -Path $AppVAddinPath
if (('.xla', '.xlam', '.xll') -NotContains $Addin.Extension) {
Write-Log 'Excel add-in extension not valid'			
} else {
try {
Write-Log 'Opening reference to Excel'
$Excel = New-Object -ComObject Excel.Application
$ExcelVersion = $Excel.Version
try {
$ExcelAddins = $Excel.Addins
$ExcelWorkbook = $Excel.Workbooks.Add()
$InstalledAddin = $ExcelAddins | ? { $_.Name -eq $Addin.Name }
if (!$InstalledAddin) {          
$NewAddin = $ExcelAddins.Add($Addin.FullName, $false)
$NewAddin.Installed = $true            			
Write-Log ('Add-in "' + $Addin.Name + '" successfully installed!')
$ExitCode = 0
} else {        
Write-Log ('Add-in "' + $Addin.Name + '" already installed!')  
$ExitCode = 0
}
} catch {
Write-Log 'Could not install the add-in: ' + $_.Exception.Message
} finally {
Write-Log 'Closing reference to Excel'
$ExcelWorkbook.Close($false)
$Excel.Quit()
if ($InstalledAddin -ne $null) {
[System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($InstalledAddin) | Out-Null
}
[System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($ExcelWorkbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($ExcelAddins) | Out-Null
[System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($Excel) | Out-Null
Remove-Variable InstalledAddin					
Remove-Variable ExcelWorkbook
Remove-Variable ExcelAddins
Remove-Variable Excel
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
} catch {
Write-Log ('Could not automate Excel add-in: ' + $_.Exception.Message)
}
}
} else {
Write-Log 'Excel add-in path not found'
}
Enable-Excel-Runvirtual
exit $ExitCode
}
function Uninstall-Addin()
{
Param(
[String]$AppVAddinPath
)    
$ExitCode = 1
$AppVPackageId = ""
$AppVVersionId = ""
$ExcelVersion = ""
$AppVCurrentUserSID = ([System.Security.Principal.WindowsIdentity]::GetCurrent()).User.Value
Write-Log ('Uninstalling: ' + $AppVAddinPath)
#If RunVirtual is configured for Excel.exe it may cause issues with COM automation, so we disable it and re-enable it later
Disable-Excel-Runvirtual
$CurrentScriptDirectory = Get-Current-Script-Directory
if (Test-Path $CurrentScriptDirectory) {
$AppVPackageId = (get-item $CurrentScriptDirectory).parent.parent
$AppVVersionId = (get-item $CurrentScriptDirectory).parent
Write-Log ('Package ID is: ' + $AppVPackageId)
Write-Log ('Version ID is: ' + $AppVVersionId)
}
if (Test-Path -Path $AppVAddinPath -PathType Leaf) {
$Addin = Get-ChildItem -Path $AppVAddinPath
if (('.xla', '.xlam', '.xll') -NotContains $Addin.Extension) {
Write-Log 'Excel add-in extension not valid'			
} else {
try {
Write-Log 'Opening reference to Excel'
$Excel = New-Object -ComObject Excel.Application           
$ExcelVersion = $Excel.Version
try {
$ExcelAddins = $Excel.Addins
$InstalledAddin = $ExcelAddins | ? { $_.Name -eq $Addin.Name }
if (!$InstalledAddin) {                      
Write-Log ('Add-in "' + $Addin.Name + '" is not installed!')  
$ExitCode = 0
} else {
$InstalledAddin.Installed = $false           			
Write-Log ('Add-in "' + $Addin.Name + '" successfully uninstalled!') 
$ExitCode = 0
}
} catch {
Write-Log 'Could not remove the add-in: ' + $_.Exception.Message
} finally {
Write-Log 'Closing reference to Excel'
$Excel.Quit()
if ($InstalledAddin -ne $null) {
[System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($InstalledAddin) | Out-Null   
}
[System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($ExcelAddins) | Out-Null    
[System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($Excel) | Out-Null                    
Remove-Variable InstalledAddin
Remove-Variable ExcelAddins
Remove-Variable Excel
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
#delete the value from Add-in Manager    
Delete-AddInRegistry -ExcelVersion $ExcelVersion -AppVCurrentUserSID $AppVCurrentUserSID -AppVAddinPath $AppVAddinPath -AppVPackageId $AppVPackageId -AppVVersionId $AppVVersionId
}
} catch {
Write-Log ('Could not automate Excel add-in: ' + $_.Exception.Message)
}
}
} else {
Write-Log 'Excel add-in path not found'       
}  
Enable-Excel-Runvirtual
exit $ExitCode
}

We run the script in a User context (because it’s writing values to HKCU) at publish time and unpublish time like so. You will need to change the path to your addin file within the virtual file system and you should be good to go!

 <UserScripts>
<PublishPackage>
<Path>powershell.exe</Path>
<Arguments>-ExecutionPolicy ByPass -WindowStyle Hidden -Command "&amp; { . '[{AppVPackageRoot}]\..\Scripts\addins.ps1'; install-addin -AppVAddinPath '[{AppVPackageRoot}]\QICharts.xla' }"</Arguments>
<Wait RollbackOnError="true" Timeout="30"/>   
</PublishPackage>
<UnpublishPackage>
<Path>powershell.exe</Path>
<Arguments>-ExecutionPolicy ByPass -WindowStyle Hidden -Command "&amp; { . '[{AppVPackageRoot}]\..\Scripts\addins.ps1'; uninstall-addin -AppVAddinPath '[{AppVPackageRoot}]\QICharts.xla' }"</Arguments>
<Wait RollbackOnError="true" Timeout="30"/>
</UnpublishPackage>
</UserScripts>

Using RunVirtual to Present Excel Addins

I’ve recently been having a look at the best way to present Excel Add Ins using our locally installed instance of Microsoft Excel and our virtualised Add Ins.

The easiest approach is probably to create an additional shortcut in your package called ‘Microsoft Excel 2010 with XXX Add In’ which points to your local instance of Excel.exe. Running this shortcut will launch the local instance of Excel.exe inside the virtual bubble, and you should then see your virtualised Add Ins when the application is loaded.

The trouble with this approach is that users will generally head straight to the main shortcut to Microsoft Excel (the shortcut to the local instance) and wonder why they can’t see their Add In. On top of this, using the approach above could mean that we have multiple ‘Microsoft Excel 2010 with XXX Add In’ shortcuts, which can look quite untidy.

An alternative approach is presented by the RunVirtual registry key introduced in App-V 5 . What this feature enables us to do is to launch the shortcut to the locally installed version of Excel and drag this process (Excel.exe) instance inside the virtual bubble of a specified package.

Again, I won’t go into too much detail here since there’s a great blog about how RunVirtual works here and also a mention of an important update for App-V 5 SP3 here, which enables us to target packages published to the User.

Anyway, I’m babbling a bit so we’ll crack on….

In my opinion RunVirtual is ALMOST great. Ideally we would be able to create a RunVirtual reference to a local application like so (Microsoft Excel in this example):

HKEY_CURRENT_USER\Software\Microsoft\AppV\Client\RunVirtual\Excel.exe

and then list multiple virtualised Addins underneath that (by specifying their PackageId and VersionId). Unfortunately we can only specify one virtual package under this key.

What this limitation means is that we will need to specify one ’empty’ virtual package which maintains a static PackageID and VersionId. This package will NEVER change. We will then use a Connection Group to connect this single package to our multiple virtualised Add In packages. What this essentially means is that when we launch the local version of Excel.exe, it will launch our associated RunVirtual package (our ’empty’ package) which in turn will launch all of its connected packages.

In honesty though, and before we continue, you would really require App-V 5 SP3 as a minimum. Why? Because the schema for connection groups has changed and it now enables us to specify OPTIONAL packages (i.e, if a connected package is not published just ignore it and don’t throw an error!) and to automatically use the latest published version of a package. For me, the ‘optional’ addition is the most important. After all, our Add Ins aren’t necessarily for all users! We may have one Add In policied to a specific group of users, and another Add In policied to another specific group of users. And membership of these policied AD Groups may very well be mutually exclusive! The optional attribute saves us a great headache in that sense and provides us with a flexibility that helps to reduce the management overhead.

Am I still babbling? Ok. An example….

Step 1. Create an ‘Empty’ Package

We will link to this from the RunVirtual key. This package isn’t entirely empty – make sure you add a dummy file to the package otherwise it will throw an error when you’re adding the package (presumably because there is no VFS to mount).

Also, ensure you allow Named Objects and COM objects to interact with the local system. They’re not both required for this example (although I think Named Objects IS required), but we need to think ahead. If we are packaging a COM Add In for Excel, this may well be required.

Finally follow this guide. This guide lets us dynamically add the relevant RunVirtual key from our AppxManifest.xml. In this example we are going to add RunVirtual in the HKCU key, since we are publishing the package to the User and NOT the machine.

Note to SCCM 2012 Virtual Environment users (and this part is all untested by myself):

As mentioned in the comments section below, when using SCCM 2012 Virtual Environments it is not a requirement that you specify a dummy package. Instead you can specify the AppConnectionGroupId and VersionId of the connection group. The problem with this approach is that the connection group gets dynamically generated on each client, which means that the AppConnectionGroupId and VersionId of the connection group is different for each client. I have suggested that to set the RunVirtual registry value dynamically you can use the approach documented here, so long as the dynamically generated connection group has a static name (I can’t verify this because I don’t have the environment to test with)!!

Get-AppvClientConnectionGroup -all | where {$_.Name -eq 'Name_Of_Connection_Group'} | foreach { New-Item -Path HKCU:\SOFTWARE\Microsoft\AppV\Client\RunVirtual -Name Excel.exe -Value ($_.AppConnectionGroupId.toString() + '_' + $_.VersionId.toString()) -Force }

The alternative approach is to use a dummy package like I have suggested in this blog, whereby the PackageId and VersionId of the dummy package remains static.

Another Note:

As Tim Mangan has mentioned in the comments section below, delivery via an App-V server differs from SCCM 2012 Virtual Environments in that it requires at least one non-optional package.

Step 2. Sequence Two Add In Packages

Each package will contain a different Excel Add In. For this proof of concept I Googled for free XLA files and created a package for each one. I simply lumped the XLA file into the following location, and that was that:

%AppData%\Microsoft\Excel\XLSTART

(You can alternatively copy it here for a per-machine install)
C:\Program Files\Microsoft Office\Office14\XLSTART

Again, ensure you allow Named Objects and COM objects to interact with the local system. We need to ensure that all of our connected packages contain the same setting for COM and Named Objects, otherwise we may get errors when launching the connection group such as:

The Application Failed to Launch. This may be due to a network failure
or
The connection group {xxx} version {xxx} could not be published because the virtual COM settings of the individual packages conflict.
Verify that the virtual COM settings are the same for all member packages and try again.

Step 3 – Create Connection Group for Local Testing

Create a connection group using this tool. You should add the ’empty’ package as being mandatory (i.e, NOT optional). The version shouldn’t matter since this package will NEVER change.

The other two Add In packages should be marked as Optional and Ignore Version.

Connection Group (Note that in your live environment you will probably just use your App-V publishing server to manage this connection group and its settings)

Step 4 – Local Testing

Ensure scripting is enabled on your machine

Set-AppvClientConfiguration -EnablePackageScripts 1

Add and Publish all your packages (since our RunVirtual key should be installing to HKCU, we publish our packages to the User and NOT the machine)

Add-AppvClientPackage -Path "Empty_Package.appv" | Publish-AppvClientPackage
Add-AppvClientPackage -Path "Addin1.appv" | Publish-AppvClientPackage
Add-AppvClientPackage -Path "Addin2.appv" | Publish-AppvClientPackage

Add and Enable our Connection Group

Add-AppvClientConnectionGroup -path "AddInCG.xml" | Enable-AppvClientConnectionGroup

Launch the local Excel version, and see your Add Ins appear!

As a further test, try un-publishing just one of the Add In packages and then launch Excel again. You will notice 2 things:

1) You don’t get an error, even though the package is not published! This is due to the Optional attribute in our connection group. So that works well.

2) You will notice that the Add In still appears in the Excel Add In tab! Doh. Why has this happened?

Well. Putting App-V aside for a second. You will notice that when you lump your XLA file into %AppData%\Microsoft\Excel\XLSTART and then launch Excel, the date modified of the XLA file changes! Close Excel and the Date Modified reverts back to its original Date Modified date. So what I believe is happening here is that App-V detects a modification to the file, and writes it to a Copy on Write location.

Importantly, it doesn’t write it to the Copy on Write location of the Add In package but instead to the Copy and Write location of the Connection Group!!

If you copied your XLA file to the per-user location (as in this example), you will find a saved XLA file in this location:

(Copy on Write (COW) Roaming) %AppData%\Microsoft\AppV\Client\VFS\{ConnectionGroupPackageId}\AppData\Microsoft\Excel\XLSTART

If you copied your XLA file to the per-machine location, you will find a saved XLA file in this location:

(Copy on Write (COW) Local) %LocalAppData%\Microsoft\AppV\Client\VFS\{ConnectionGroupPackageId}\ProgramFilesX86\Microsoft Office\Office14\XLSTART

So in essence, when you un-publish your Add In packages you will need to add some logic to delete this file.

Configuring RunVirtual Registry from the AppxManifest.xml

To configure the RunVirtual key from an App-V package we needed to add a registry key/value to the local machine via an App-V script. Since our environment is VDI we are using a new feature in App-V 5 SP3 which enables us to target an App-V application which is published to the user (and not to the machine).

I won’t discuss what RunVirtual is – you can read this if you need to know.

The key reason for this tutorial is because we are performing this logic in the AppxManifest.xml and NOT the UserConfig.xml. I prefer to add my logic to AppxManifest.xml since it makes my package self-contained with no reliance on external (xml) files – see here.

Why is adding RunVirtual registry logic to the AppxManifest.xml different from adding it to the UserConfig.xml?

Well first a very brief bit of background – the registry key/value we need to add is:

HKCU\SOFTWARE\Microsoft\AppV\Client\RunVirtual\{LocalProcess.exe}

(Default) REG_SZ {PackageId}_{VersionId}

By creating a key under RunVirtual with the process name (Excel.exe in this case) we effectively saying “whenever we run Excel.exe I want it to run in the same virtual space as the App-V package with the specified PackageId_VersionId”. As you’ve probably gathered, this specific package was an Addin for Microsoft Excel.

Adding the logic via a UserConfig.xml script is relatively trivial. We can open the XML file in a text editor and add the following:

   <UserScripts>
<PublishPackage>
<Path>cmd.exe</Path>
<Arguments>/C REG ADD HKCU\SOFTWARE\Microsoft\AppV\Client\RunVirtual\Excel.exe /ve /d "2a04d2aa-ad7a-4c51-b774-00ca0e1e1fad_e2ce036d-9cbf-479d-9b67-8b4648742e8c" /f</Arguments>
<Wait RollbackOnError="true" Timeout="30"/>
</PublishPackage>
<UnpublishPackage>
<Path>cmd.exe</Path>
<Arguments>/C REG DELETE HKCU\SOFTWARE\Microsoft\AppV\Client\RunVirtual\Excel.exe /f</Arguments>
<Wait RollbackOnError="false" Timeout="30"/>
</UnpublishPackage>
</UserScripts>

However adding a similar change via the AppxManifest.xml is slightly more complex. Whenever we import a new AppxManifest.xml via the Advanced tab in Edit mode, we need to save the package afterwards for the change to take effect. And what does this do? It flips the VersionId of the package! So we’re left with a situation whereby whenever we update the VersionId in the AppxManifest.xml it becomes redundant as soon as we save the updated package!

Introducing the Powershell approach….

Ignore the slightly different XML syntax for now – this is irrelevant in the context of this tutorial.

<appv:UserScripts>
<appv:PublishPackage>
<appv:Path>powershell.exe</appv:Path>
<appv:Arguments>-ExecutionPolicy ByPass -command "Get-AppvClientPackage -all | where {$_.Name -eq 'Alkane_Package'} | foreach { New-Item -Path HKCU:\SOFTWARE\Microsoft\AppV\Client\RunVirtual -Name Excel.exe -Value ($_.PackageId.toString() + '_' + $_.VersionId.toString()) -Force }"</appv:Arguments>
<appv:Wait RollbackOnError="false" />
</appv:PublishPackage>
<appv:UnpublishPackage>
<appv:Path>powershell.exe</appv:Path>
<appv:Arguments>-ExecutionPolicy ByPass -command "Remove-Item -Path HKCU:\SOFTWARE\Microsoft\AppV\Client\RunVirtual\Excel.exe -Recurse -Force"</appv:Arguments>
<appv:Wait RollbackOnError="false" />
</appv:UnpublishPackage>
</appv:UserScripts>

The main part to note is the Powershell one-liner, which I’ll explain below:

Get-AppvClientPackage -all | where {$_.Name -eq 'Alkane_Package'} | foreach { New-Item -Path HKCU:\SOFTWARE\Microsoft\AppV\Client\RunVirtual -Name Excel.exe -Value ($_.PackageId.toString() + '_' + $_.VersionId.toString()) -Force }

What we’re doing here is:

  • Getting all locally ADDED packages (Note that Get-AppvClientPackage -Name “Alkane_Package” will return nothing since the package is not published when the PublishPackage event runs!)
  • Looping through them where the Name attribute is the name of my target package (Alkane_Package)
  • We then use foreach because of this
  • And then we can dynamically get the PackageId and VersionId of the package to add it to the registry value