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 "& { . '[{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 "& { . '[{AppVPackageRoot}]\..\Scripts\addins.ps1'; uninstall-addin -AppVAddinPath '[{AppVPackageRoot}]\QICharts.xla' }"</Arguments>
<Wait RollbackOnError="true" Timeout="30"/>
</UnpublishPackage>
</UserScripts>
[…] already discussed how we can use App-V 5 to package Excel automation addins with RunVirtual – it’s fairly complex since during registration (and unregistration) of the add-in it […]