App-V 5 with Excel Automation Addins and RunVirtual

Posted on by
Reply

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>

Configuring Excel Automation Add-ins with VBScript

Posted on by
Reply

This is a replica (with better formatting) of a post I made over at ITNinja (or AppDeploy back then!) several years ago which discusses configuring Excel automation add-ins.  I’m not even sure if it still works but it’s great as a reference point, so use it at you own risk.

This 5-step tutorial on configuring Excel automation add-ins enables XLA, XLL, XLAM and even COM addins to be automatically installed/removed if they are included in your Windows Installer package. The Custom Actions (CA) will either install EVERY excel add-in in the installer, or just add-ins in a specific directory depending upon how you configure it.

This version:

  • removes the add-in for multiple office versions (97 To 2010)
  • removes the add-in for multiple profiles IF DESIRED
  • installs XLAM add-ins

It still outputs any debugging messages to the windows installer log file. Each debugging line starts with either ‘AddExcelAddinStatus’ or ‘RemoveExcelAddinStatus:’.

This version also contains separate CAs for adding and removing the add-in, because when adding/removing COM add-ins the automation needs to be placed in specific parts of the InstallExecuteSequence.

Configuring Excel Automation Add-ins Step by Step

Step 1 – Create Two Properties

  • Create a property called ‘installAddin’. Give it a default value. I gave mine ‘noaddin’ (It doesn’t really matter what default value you give it)
  • Create a property called ‘removeAddin’. Give it a default value. I gave mine ‘noaddin’

Step 2 – Create Custom Action for Add-In Selection

We can either install every single add-in in the installer, or only install the add-ins which are present in a specified directory (See red font in code)

  • Create a type 38 CA (Embedded VBScript). Call it ‘setAddinProperty’.
  • Schedule it as Immediate, just before InstallInitialize. Do not give it a condition. We want it to Execute on install, uninstall and repair.

Paste the following code into your CA (You should only need to edit the values of blnfilterByDirectory and/or filterDirectory. LEAVE EVERYTHING ELSE ALONE.):

'set blnfilterByDirectory to True if you want to install all add-ins in a specific directory (also specify the directory name below)
'set blnfilterByDirectory to False if you want to install every single add-in in the Installer
Dim blnfilterByDirectory : blnfilterByDirectory = True
'***Important - This directory name is case-sensitive!!!
Dim filterDirectory : filterDirectory = "INSTALLDIR"

'*************************************
'*****DO NOT EDIT BELOW THIS LINE
'*************************************

Dim tempFileName : tempFileName = ""
Dim tempComponent : tempComponent = ""
Dim addinList : addinList = ""
Dim tempExtension : tempExtension = ""

'If we're filtering by directory, construct the sql command accordingly
If blnfilterByDirectory Then
	sql = "SELECT File.Component_,File.FileName,Component.Directory_ FROM File, Component WHERE File.Component_ = Component.Component AND Component.Directory_ = '" & filterDirectory & "'"
Else
	sql = "SELECT File.Component_,File.FileName,Component.Directory_ FROM File, Component WHERE File.Component_ = Component.Component"
End If

'start searching through file table for add-ins (.XLA or .XLL files)
Set fileView= Session.Database.OpenView(sql)
fileView.Execute
Set fileRecord = fileView.Fetch
While Not fileRecord Is Nothing
	tempFileName = LCase(fileRecord.StringData(2))
	If InStr(tempFileName,"|") Then 'if filename is currently in sfn form, try and retrieve the full file name
		tempFileName = Split(tempFileName,"|")(1)
	End If
	If InStr(tempFileName,".") Then
		tempExtension = Split(tempFileName,".")(1)
	End If
	
	If (tempExtension = "xla" Or tempExtension = "xll" Or tempExtension = "xlam") Then 'its an excel addin
'construct list of addins, delimited by commas
		addinList = addinList & Session.Property(fileRecord.StringData(3)) & tempFileName & ","
	End If
	Set fileRecord = fileView.Fetch
Wend

Set fileView = Nothing
Set fileRecord = Nothing

'remove trailing comma
If Len(addinList) > 0 Then
	addinList = Left(addinList,Len(addinList)-1)
End If

Property("installAddin") = CStr(addinList)
Property("removeAddin") = CStr(addinList)

'update windows installer session environment and current process with any
'path environment variables found in environment table

Dim tempName : tempName = ""
Dim tempValue : tempValue = ""
Dim tempEnvPath : tempEnvPath = ""


sql = "SELECT Name, Value FROM Environment"

Set envView= Session.Database.OpenView(sql)
envView.Execute
Set envRecord = envView.Fetch
While Not envRecord Is Nothing
	
	tempName = envRecord.StringData(1)
	tempValue = envRecord.StringData(2)
	
	If Not Instr(tempName,"!") > 0 Then
'if we're not removing env var on installation
		
		tempName = replace(tempName,"=","")
		tempName = replace(tempName,"+","")
		tempName = replace(tempName,"-","")
		tempName = replace(tempName,"*","")
		
		If lcase(tempName) = "path" Then
			
			If right(tempValue,3) = "[~]" Then
'prefix
				tempValue = replace(tempValue,"[~]","")
				tempEnvPath = returnEnvironmentPath(tempValue) & ";" & Session.Installer.Environment("Path")
			ElseIf left(tempValue,3) = "[~]" Then
'suffix
				tempValue = replace(tempValue,"[~]","")
				tempEnvPath = Session.Installer.Environment("Path") & ";" & returnEnvironmentPath(tempValue)
			Else
'replacement, which 'should' never happen with the path var, but for this we'll set as prefix
				tempEnvPath = returnEnvironmentPath(tempValue) & ";" & Session.Installer.Environment("Path")
			End If
'replace any double-semis
			tempEnvPath = replace(tempEnvPath,";;",";")
'set session env path
			Session.Installer.Environment("Path") = tempEnvPath
			
'make the relevant Path env var available to current process (and processes spawned therein)
			Set oShell = CreateObject("WScript.Shell")
			Set oProcessEnv = oShell.Environment("PROCESS")
			oProcessEnv("Path") = tempEnvPath
			Set oProcessEnv = Nothing
			Set oShell = Nothing
			
			
			
		End If
	End If
	
	Set envRecord = envView.Fetch
Wend

Set envView = Nothing
Set envRecord = Nothing



'Function to return 'proper' path for env var
Function returnEnvironmentPath(envPath)
	
	Set objRE = New RegExp
	With objRE
		.Pattern = "\[.+\]" 'match anything inside and including square brackets Eg [WindowsVolume]
		.IgnoreCase = True
		.Global = False 'return one instance
	End With
	
' Test method returns TRUE if a match is found
	If objRE.Test(envPath) Then
		
		Set objMatch = objRE.Execute(envPath)
		strProperty = objMatch.Item(0)
		Set objMatch = Nothing
'perform the replacement
		strEnvPath = objRE.Replace(envPath, Session.Property(Mid(strProperty,2,Len(strProperty)-2)))
		returnEnvironmentPath = strEnvPath
	Else
		returnEnvironmentPath = envPath
	End If
	
	Set objRE = Nothing
	
End Function

Step 3 – Create CA to install addin

  • Create another Type 38 CA. Call it ‘installAddin’.
  • Schedule it straight after ScheduleReboot, Deferred in a User Context (Setting it as deferred etc makes the Type become 1062 in your CA table).
    Give it a condition of:

NOT Installed Or MaintenanceMode=”Modify”

  • Paste the following code into your CA:
'*************************************
'logic to install addin (can be used for automation addins or COM addins)
'(All status messages are printed to installer log)
'(All log status entries start with 'AddExcelAddinStatus: {status}')
'*************************************

Dim blnReturn : blnReturn = False
Dim objXL
Dim objWorksheet
Dim objAddin
Dim strAddIn : strAddIn = ""
Dim strMsg : strMsg = ""
Dim strAddInName : strAddInName = ""
Dim addinList : addinList = ""
Dim addinListArray : addinListArray = ""
Dim i : i = 0
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_CURRENT_USER = &H80000001
Dim strFirstRun : strFirstRun = ""
Dim strUserData : strUserData = ""
Dim strFirstRunValueName : strFirstRunValueName = ""
Dim blnFoundFirstRun : blnFoundFirstRun = False
Dim dwValue : dwValue = ""
Dim strComputer : strComputer = "."
Dim objRegistry
Dim officeversion
Dim keyCount : keyCount = 0
Dim keyArray(14)
Dim valueCount : valueCount = 0
'cannot redim a multi-dim array so we set the size statically
Dim valueArray(9,1)


'retrieve the value of the property we set earlier
'(The value is comma-separated in the form 'featureInstallState, Addin1, Addin2, Addin3......' etc)
addinList = Session.Property("CustomActionData")
'write value of Session Property to log for debugging purposes
writeToLog("Deferred property contains: " & addinList)
If Len(addinList) > 0 Then 'if we found an add-In
	Set objRegistry= GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
	
'see if Excel has been opened before
	For officeversion = 8 to 14
		strFirstRun = "Software\Microsoft\Office\" & officeversion & ".0\Excel\Options"
		
		objRegistry.EnumValues HKEY_CURRENT_USER, strFirstRun, arrValueNames, arrValueTypes
'check if a value is returned
		If IsArray(arrValueNames) Then
'if so, loop through values in the registry key
			For a=0 To UBound(arrValueNames)
				strFirstRunValueName = arrValueNames(a)
'if the value is 'FirstRun', read it
				If UCase(strFirstRunValueName) = "FIRSTRUN" Then
					objRegistry.GetDWORDValue HKEY_CURRENT_USER,strFirstRun,strFirstRunValueName,dwValue
'if value is not zero, it's not been run for the first time, so we automate it
					If CInt(dwValue) <> 0 Then
						writeToLog("Excel has not been run for the first time....Firstrun value exists but is not '0'. Setting UserData value to 1....")
					End If
'foudn a firstrun entry
					blnFoundFirstRun = True
				End If
			Next
		End If
	Next
	Set objRegistry= Nothing
	
	If Not blnFoundFirstRun Then
'havent found any firstrun value, so excel has not been run
		writeToLog("Excel has not been run for the first time....Firstrun value does not exist. Attempting to set UserData value....")
		setUserData()
	End If
	
	
'retrieve addin list
	addinListArray = split(addinList,",")
	
'for every addin, try and add it
	For i = 0 To UBound(addinListArray)
		
'get individual addin full path
		strAddInName = Trim(addinListArray(i))
		
		blnReturn = AddExcelAddin(strAddInName)
		If Not blnReturn Then
			strMsg = "Unable to install Excel add-in '" & strAddInName & "'"
			writeToLog(strMsg)
		End If
	Next
	
	
	If Not blnFoundFirstRun Then
'resets registry keys so Excel heals agian on first launch
		revertRegistry()
	End If
	
	
Else
	strMsg = "No add-ins were found. If you are installing add-ins from a specific directory, check the case of your specified directory in the setAddinProperty CA."
	writeToLog(strMsg)
End If

'create and delete a system environment variable to ensure any system environment vars installed with the package
'successfully update on the target system without a reboot

Set wshshell = CreateObject("WScript.Shell")
Set WshSysEnv = wshShell.Environment("SYSTEM")

WshSysEnv("FlushEnvironment") = "default"
WshSysEnv.Remove("FlushEnvironment")

Set WshSySEnv = Nothing
Set wshshell = Nothing

Function setUserData()
	
'If we write UserData value, Excel will not self-heal if it has not been loaded before. However, if we keep
'the FirstRun value as either not existing, or set to a non-zero value, Excel will still heal when manually loaded.
	
	Set objRegistry= GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
	
	For oversion = 8 To 14
		strUserData = "Software\Microsoft\Office\" & oversion & ".0\"
		If objRegistry.EnumKey (HKEY_LOCAL_MACHINE, strUserData & "Excel", arrValueNames) = 0 Then
'if reg key exists, write UserData in HKCU
'create registry key
			createRegistryKey HKEY_CURRENT_USER, strUserData & "Excel"
'write dword value
			createRegistryValue HKEY_CURRENT_USER,strUserData & "Excel","UserData",1
			
		End If
		
		If objRegistry.EnumKey (HKEY_LOCAL_MACHINE, strUserData & "Common", arrValueNames) = 0 Then
'if reg key exists, write UserData in HKCU
'create registry key
			createRegistryKey HKEY_CURRENT_USER, strUserData & "Common"
'write dword value
			createRegistryValue HKEY_CURRENT_USER,strUserData & "Common","UserData",1
			
'create registry key
			createRegistryKey HKEY_CURRENT_USER, "Software\ODBC\ODBC.INI\MS Access Database"
		End If
		
	Next
	
	Set objRegistry= Nothing
End Function


Function createRegistryKey(hive, path)
	
	If objRegistry.EnumKey (hive, path, arrValueNames) <> 0 Then
'reg key does not exist
		return = objRegistry.CreateKey(hive, path)
		If (return = 0) And (Err.Number = 0) Then
			writeToLog("Created 'HKCU\" & path & "' registry key...")
			keyArray(keyCount) = path
			keyCount = keyCount + 1
		Else
			writeToLog("Error creating 'HKCU\" & path & "' registry key...")
			On Error GoTo 0
		End If
	End If
End Function

Function deleteRegistryKey(hive, path)
	
	If objRegistry.EnumKey (hive, path, arrValueNames) = 0 Then
'reg key exists
		return = objRegistry.DeleteKey(hive, path)
		If (return = 0) And (Err.Number = 0) Then
			writeToLog("Deleted 'HKCU\" & path & "' registry key...")
		Else
			writeToLog("Error deleting 'HKCU\" & path & "' registry key...")
			On Error GoTo 0
		End If
	End If
	
End Function

Function createRegistryValue(hive, path, valuename, valuedata)
	
	objRegistry.GetDWORDValue hive,path,valuename,valuedata
	
	If IsNull(valuedata) Then
		return = objRegistry.SetDWORDValue(hive,path,valuename,valuedata)
		If (return = 0) And (Err.Number = 0) Then
			writeToLog("Created 'HKCU\" & path & "\" & valuename & "' value...")
			
			valueArray(valueCount,0) = path
			valueArray(valueCount,1) = valuename
			valueCount = valueCount + 1
		Else
			writeToLog("Error creating 'HKCU\" & path & "\" & valuename & "' value...")
			On Error GoTo 0
		End If
	End If
End Function

Function deleteRegistryValue(hive, path, valuename)
	
	objRegistry.GetDWORDValue hive,path,valuename,valuedata
	
	If Not IsNull(valuedata) Then
		return = objRegistry.DeleteValue(hive,path,valuename)
		If (return = 0) And (Err.Number = 0) Then
			writeToLog("Deleted 'HKCU\" & path & "\" & valuename & "' value...")
		Else
			writeToLog("Error deleting 'HKCU\" & path & "\" & valuename & "' value...")
			On Error GoTo 0
		End If
	End If
End Function

'*******************************************
'This function installs the Excel Addin
'*******************************************
Function AddExcelAddin(ByVal strAddIn)
	Dim objFSO_XL
	Dim intCounter : intCounter = 0
	Dim blnInstalledAlready : blnInstalledAlready = False
	Dim addinName : addinName = Right(strAddIn,Len(strAddIn)-InStrRev(strAddIn,"\"))
	AddExcelAddin = False
	
	Set objFSO_XL = CreateObject("Scripting.FileSystemObject")
	With objFSO_XL
		strMsg = ""
		On Error Resume Next
'Check source file exists
		If Not .FileExists(strAddIn) Then
			strMsg = "The source file " & strAddIn & " does not exist." & VbCrLf & "'" & strAddIn & "' was not installed."
			writeToLog(strMsg)
			Exit Function
		End If
		On Error GoTo 0
	End With
	
	On Error Resume Next
'create Excel object
	Set objXL = CreateObject("Excel.Application")
	If Err.Number <> 0 Then
		strMsg = "Failed to create Excel object." & VbCrLf
		strMsg = strMsg & "'" & strAddIn & "' was not installed."
		
		writeToLog(strMsg)
		On Error GoTo 0
	Else
		strMsg = "Created Excel object."
		writeToLog(strMsg)
	End If
'add workbook
	Set objWorksheet = objXL.Workbooks.Add()
	If Err.Number <> 0 Then
		strMsg = "Failed to create new workbook." & VbCrLf
		strMsg = strMsg & "'" & strAddIn & "' was not installed."
		
		writeToLog(strMsg)
		On Error GoTo 0
	Else
		strMsg = "Created worksheet object."
		writeToLog(strMsg)
	End If
	
'try and add addin
	With objXL
		For intCounter = 1 to .Addins.Count
			If LCase(.Addins(intCounter).Name) = LCase(addinName) Then
				If .Addins.Item(intCounter).Installed Then
					blnInstalledAlready = True
					AddExcelAddin = True
					Exit For
				End If
			End If
		Next
		
		If Not blnInstalledAlready Then
			Set objAddin = .AddIns.Add(strAddIn)
			If Err.Number <> 0 Then
				strMsg = ""
				strMsg = strMsg & "Error: " & Err.Description & vbCRLF
				strMsg = strMsg & "Failed to add add-in '" & strAddIn & "'." & vbCRLF & "'" & strAddIn & "' was not installed."
				writeToLog(strMsg)
				On Error GoTo 0
			Else
				objAddin.Installed = True
				If Err.Number <> 0 Then
					strMsg = ""
					strMsg = strMsg & "Error: " & Err.Description & vbCRLF
					strMsg = strMsg & "Failed to set add-in installed status." & vbCRLF & "'" & strAddIn & "' was not installed."
					writeToLog(strMsg)
				Else
					strMsg = "Add-in '" & strAddIn & "' installed successfully."
					AddExcelAddin = True
					writeToLog(strMsg)
				End If
			End If
		Else
			strMsg = "Add-in '" & strAddIn & "' is already installed." & vbCRLF & "'" & strAddIn & "' was not installed."
			writeToLog(strMsg)
		End If		
		
	End With
	Set objWorksheet = Nothing
	objXL.Quit
	Set objFSO_XL = Nothing
	Set objAddin = Nothing
	Set objXL = Nothing	
	
End Function

Function revertRegistry()
	
	Set objRegistry= GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
	
'deleteRegistryKey(hive, path)
	For i = 0 to ubound(keyArray)
		If Not CStr(keyArray(i)) = "" Then
			deleteRegistryKey HKEY_CURRENT_USER, CStr(keyArray(i))
		End If
	Next
	
'deleteRegistryValue(hive, path, valuename)
	For i = 0 to UBound(valueArray)
		If Not CStr(valueArray(i,0)) = "" Then
			deleteRegistryValue HKEY_CURRENT_USER, CStr(valueArray(i,0)), CStr(valueArray(i,1))
		End If
	Next
	
	Set objRegistry= Nothing
	
End Function


Const msiMessageTypeInfo = &H04000000
'Subroutine to write to log file
Sub writeToLog(ByVal msg)
	Set record = Installer.CreateRecord(1)
	record.stringdata(0) = "AddExcelAddinStatus: [1]"
'This value gets subbed in to the [1] placeholder above
	record.stringdata(1) = msg
	record.formattext
	message msiMessageTypeInfo, record
	Set record = Nothing
End Sub

Step 4 – Create Custom Action to Uninstall Add-In

  • Now create another Type 38 CA. Call it ‘removeAddin’.
  • Schedule it straight after InstallInitialize and make it Deferred in a User Context (Setting it as deferred etc makes the Type become 1062 in your CA table).
    Give it a condition of:

REMOVE~=”ALL”

  • Paste the following code into your CA (You should only need to edit the value of blnDeleteFromAllProfiles. LEAVE EVERYTHING ELSE ALONE.):
'*************************************
'logic to uninstall addin (can be used for automation addins or COM addins)
'(All status messages are printed to installer log)
'(All log status entries start with 'RemoveExcelAddinStatus: {status}')
'*************************************

'set this to true/false depending on whether you want to attempt to delete the HKCU\xxxx\OPENx value from each user profile
'true = delete from all profiles false=delete from current profile only
Dim blnDeleteFromAllProfiles : blnDeleteFromAllProfiles = False

Dim blnReturn : blnReturn = False
Dim objXL
Dim objWorksheet
Dim objAddin
Dim strAddIn : strAddIn = ""
Dim strMsg : strMsg = ""
Dim strAddInName : strAddInName = ""
Dim addinList : addinList = ""
Dim addinListArray : addinListArray = ""
Dim i : i = 0
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Dim tempRelativeId : tempRelativeId = 0
Dim strComputer : strComputer = "."
Dim strAddinKeyPath, strAddinValueName
Dim strValueName : strValueName = ""
Dim objRegistry, objFSO, objWshShell


'retrieve the value of the property we set earlier
'(The value is comma-separated in the form 'featureInstallState, Addin1, Addin2, Addin3......' etc)
addinList = Session.Property("CustomActionData")
'write value of Session Proeprty to log for debugging purposes

writeToLog("Deferred property contains: " & addinList)

If Len(addinList) > 0 Then 'if we found an add-In
	
	addinListArray = split(addinList,",")
	
'for every addin passed in our property
	For i = 0 To UBound(addinListArray)
		strAddInName = addinListArray(i)
		
'we're uninstalling
		blnReturn = RemoveExcelAddin(strAddInName)
		
		If Not blnReturn Then
			strMsg = "Unable to uninstall Excel add-in '" & strAddInName & "'"
			writeToLog(strMsg)
		Else
'now it's uninstalled we attempt to remove keys from add-in manager
'we do it here because it only gets generated after uninstall when our reference to Excel.Application is closed
			Set objRegistry= GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
			Set objFSO = CreateObject("Scripting.FileSystemObject")
			Set objWshShell = CreateObject("WScript.Shell")
			
'delete for current user
			deleteFromProfile HKEY_CURRENT_USER,""
			
			If blnDeleteFromAllProfiles Then
				
'try deleting key from all profiles
'profilelist reg key contains profiles which have logged on to the machine (and some default profiles too)
				Dim strProfileListKeyPath
				strProfileListKeyPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList"
				objRegistry.EnumKey HKEY_LOCAL_MACHINE, strProfileListKeyPath, arrSubkeys
				
				Dim arrSubkeys, objSubkey, strProfileValueName, strSubPath, ntuserfile, userfolder, officeversion, strOptionsKeyPath
				Dim arrValueNames, arrValueTypes, strOptionsValueName, strValue, a
				
'enumerate all SIDs in profile list (profiles which have logged on to machine)
				For Each objSubkey In arrSubkeys
					tempRelativeId = Split(objSubkey,"-")
					
'check its not one of the default SIDs
					If nonDefaultRelativeId(tempRelativeId(UBound(tempRelativeId))) Then
						
						strProfileValueName = "ProfileImagePath"
						strSubPath = strProfileListKeyPath & "\" & objSubkey
						objRegistry.GetExpandedStringValue HKEY_LOCAL_MACHINE,strSubPath,strProfileValueName,userfolder
						ntuserfile = userfolder & "\ntuser.dat"
						
'check the ntuser.dat file exists before we temporarily import it
						If objFSO.fileExists(ntuserfile) Then
							deleteFromProfile HKEY_USERS,ntuserfile
						End If
					End If
				Next
				
				Set objRegistry = Nothing
				Set objFSO = Nothing
				Set objWshShell = Nothing
				
			End If
			
		End If
	Next
Else
	strMsg = "No add-ins were found. If you are installing add-ins from a specific directory, check the case of your specified directory in the setAddinProperty CA."
	writeToLog(strMsg)
End If



'*******************************************
'this function unloads and then deletes the add-in from the add-in manager.
'*******************************************

Function deleteFromProfile(HIVEKEY,ntuserfile)
	
	On Error Resume Next
	
	If Not ntuserfile = "" Then
		objWshShell.Run "Reg.exe load HKEY_USERS\tempRegistry " & chr(34) & ntuserfile & chr(34), 0, True
		strMsg = "Attempting to remove Add-in for ntuser file: " & ntuserfile
		writeToLog(strMsg)
	Else
		strMsg = "Attempting to remove Add-in for current user"
		writeToLog(strMsg)
	End If
	
'unload and delete from add-in list for Office 97 to 2010
	For officeversion = 8 to 14
		strOpenKeyPath = "Software\Microsoft\Office\" & officeversion & ".0\Excel\Options"
		strAddinKeyPath = "Software\Microsoft\Office\" & officeversion & ".0\Excel\Add-in Manager"
		
		If Not ntuserfile = "" Then
			strOpenKeyPath = "tempRegistry\" & strOpenKeyPath
			strAddinKeyPath = "tempRegistry\" & strAddinKeyPath
		End If
		
'unload from addin manager (delete OPENx value)
		
		objRegistry.EnumValues HIVEKEY, strOpenKeyPath, arrValueNames, arrValueTypes
'check if a value is returned
		If IsArray(arrValueNames) Then
'if so, loop through values in the registry key
			For a=0 To UBound(arrValueNames)
				strOpenValueName = arrValueNames(a)
'if the value starts with 'OPEN', then its an addin
				If Left(UCase(strOpenValueName),4) = "OPEN" Then
					objRegistry.GetStringValue HIVEKEY,strOpenKeyPath,strOpenValueName,strValue
'we check the OPEN value to see if it's our addin that we need to remove
					If InStr(1,strValue,strAddInName,1) > 0 Then
						strMsg = "Unloading: " & Replace(strOpenKeyPath,"tempRegistry\","") & "\" & strOpenValueName
						writeToLog(strMsg)
'If it is, we delete it
						objRegistry.DeleteValue HIVEKEY,strOpenKeyPath,strOpenValueName
						
						If Err.Number <> 0 Then
							strMsg = "Unloaded: " & strOpenKeyPath & "\" & strOpenValueName
							writeToLog(strMsg)
						Else
							strMsg = "Could not unload: " & strOpenKeyPath & "\" & strOpenValueName
							writeToLog(strMsg)
'reset error handling
							On Error GoTo 0
						End If
						
					End If
				End If
			Next
		End If
		
'delete from addin manager
		
		objRegistry.EnumValues HIVEKEY, strAddinKeyPath, arrValueNames, arrValueTypes
'check if a value is returned
		
		If isArray(arrValueNames) Then
'if so, loop through values in the registry key
			For a=0 To UBound(arrValueNames)
				strAddinValueName = arrValueNames(a)
'if the value name is the same as our addin
				If InStr(1,strAddinValueName,strAddInName,1) > 0 Then
					strMsg = "Deleting: " & Replace(strAddinKeyPath,"tempRegistry\","") & "\" & strAddinValueName
					writeToLog(strMsg)
'If its the addin, we delete it
					objRegistry.DeleteValue HIVEKEY,strAddinKeyPath,strAddinValueName
					
					If Err.Number <> 0 Then
						strMsg = "Deleted: " & strAddinKeyPath & "\" & strAddinValueName
						writeToLog(strMsg)
					Else
						strMsg = "Could not delete: " & strAddinKeyPath & "\" & strAddinValueName
						writeToLog(strMsg)
'reset error handling
						On Error GoTo 0
					End If
					
				End If
			Next
		End If
	Next
	
	If Not ntuserfile = "" Then
		objWshShell.Run "Reg.exe unload HKEY_USERS\tempRegistry", 0, True
	End If
	
'reset error handling
	On Error GoTo 0
	
End Function



'*******************************************
'Any group or user that is not created by default will have a Relative ID of 1000 or greater.
'The last hyphen-separated value in a SID is the relative id. This function omits these accordingly
'*******************************************


Function nonDefaultRelativeId(relativeId)
	
	nonDefaultRelativeId = False
	
	If IsNumeric(relativeId) Then
		If relativeId >= 1000 Then
			nonDefaultRelativeId = True
		End If
	End If
	
End Function


'*******************************************
'This function removes the Excel Addin
'*******************************************

Function RemoveExcelAddin(ByVal strAddIn)
	
	Dim intCounter : intCounter = 0
	Dim blnInstalled : blnInstalled = False
	Dim addinName : addinName = Right(strAddIn,Len(strAddIn)-InStrRev(strAddIn,"\"))
	RemoveExcelAddin = False
	
	
	On Error Resume Next
	Set objXL = CreateObject("Excel.Application")
	If Err.Number <> 0 Then
		strMsg = "Failed to create Excel object." & VbCrLf
		strMsg = strMsg & "'" & strAddIn & "' was not installed."
		writeToLog(strMsg)
	Else
		strMsg = "Created Excel object."
		writeToLog(strMsg)
	End If
	
'reset error handling
	On Error GoTo 0
	
	With objXL
		For intCounter = 1 To .Addins.Count
			If LCase(.Addins(intCounter).Name) = LCase(addinName) Then
				If .Addins.Item(intCounter).Installed Then
					blnInstalled = True
					Exit For
				End If
			End If
		Next
		
		If blnInstalled Then
'intCounter ought still to be at the correct position,
'since we exited the For...Next loop when we located the add-in
			.Addins.Item(intCounter).Installed = False
			If Err.Number <> 0 Then
				strMsg = ""
				strMsg = strMsg & "Error: " & Err.Description & vbCRLF
				strMsg = strMsg & "Failed to remove add-in '" & strAddIn & "'." & vbCRLF & "'" & strAddIn & "' was not removed."
				
				writeToLog(strMsg)
				
'reset error handling
				On Error GoTo 0
			Else
				strMsg = "Add-in '" & strAddIn & "' removed successfully."
				blnInstalled = False
				RemoveExcelAddin = True
				writeToLog(strMsg)
			End If
		Else
			strMsg = "Add-in '" & strAddIn & "' is not installed, so no removal necessary." & vbCRLF & "'" & strAddIn & "' was not removed."
			
			writeToLog(strMsg)
'we return true so that the relevant OPENx keys are removed
			RemoveExcelAddin = True
		End If
		
	End With
	
	objXL.Quit
	Set objAddin = Nothing
	Set objXL = Nothing
	
End Function


Const msiMessageTypeInfo = &H04000000

'Subroutine to write to log file
Sub writeToLog(ByVal msg)
	Set record = Installer.CreateRecord(1)
	record.stringdata(0) = "RemoveExcelAddinStatus: [1]"
'This value gets subbed in to the [1] placeholder above
	record.stringdata(1) = msg
	record.formattext
	message msiMessageTypeInfo, record
	Set record = Nothing
End Sub