Scripting

***UPDATED VERSION***

This 5-step tutorial enables excel add-ins (XLA, XLL, XLAM) to be automatically installed/removed if they are included in your Windows Installer package. The Custom Actions (CA) will either install EVERY excel addin in the installer, or just addins in a specific directory.

The first version struggled with COM addins (worked best for automation addins) whereas this version should also work well with COM addins. This version also:

removes the addin for multiple office versions (97 To 2007)
removes the addin for multiple profiles IF DESIRED
installs XLAM addins

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

This version contains separate CAs for adding and removing the addin, because when adding/removing COM addins the automation needs to be placed in specific parts of the IESequence.

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 CA for add-ins 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 FunctionStep 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 12
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 12
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 CA to uninstall addin

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 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 2007
For officeversion = 8 to 12
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
Step 5 - Check Load Behaviour of addin

For COM Addins, you'll find that it writes a key similar to this:

HKCU\Software\Microsoft\Office\Excel\Addins\<ProgId>

Under this key will be a value called 'LoadBehaviour' and it will most probably have a value of 3 (Load automatically).
This can cause problems when using automation to install/load a COM Addin, so I reccommend setting this to '0' in your installer (Unloaded/Don't load automatically)

Thanks, Mark. The bug should be fixed.....i think.

You could always write a CA to set the path environment variable from a CA? And execute it just before 'installAddin' in a deferred context. I'm not sure if it's great practice though. Somebody else may be able to answer that. But if you did, you'd need to write one which removes it on uninstall/rollback too, you'd need to see if it should be appended as a prefix or a suffix, and it would need to be VERY robust.....as a quick example:

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

Dim Path : Path = WshSysEnv("PATH")
Dim PathToAdd : PathToAdd = "c:\whatever\whatever"

If InStr(1, Path, PathToAdd, 1) = 0 Then
WshSysEnv.Item("Path") = Path & ";" & PathToAdd
If Err.Number <> 0 Then
'handle error
End If
End If

Set WshShell = Nothing
Set WshSysEnv = Nothing


Hello is not better to create a customs with the command line with the VSTO ?

Nice one, Cap'n. Wanna ask Bob if he wants to make it a 'Sticky'?

Cheers. I'll whack a link to it in that existing sticky for now.... [;)]

Thanks for providing the info captain!

One thought...

Is it not the case that add-ins are provided are in XLL form, as well as XLA? The code will need tweaking to accomodate those...

Thanks, AngelD. VBScab - i think you're probably right. Thanks for noticing. I've just made the tweak to the original code.....[;)]

Nice scripts, thanks.

For many excel add-ins the problem is that the users rarely run the 'main' application that would trigger a repair of the add-in they just start excel and expect the add-in to already be there. To make that happen something needs to install the add-in for each user which would typically be ActiveSetup. Using ActiveSetup to call a repair of a MSI with these custom actions would work nicely.

Hi all,

I'm using these scripts and have to say it's great that they have been provided.
I added them to my msi yesterday and installed my Add-ins without a problem.

However, this morning, after reverting my VMware snapshot so I can do another test I'm now receiving a Runtime Error 53 when trying to install the addins using the scripts in Custom Actions.

I have checked and the files do exist on the machine when it is trying to add them as addins.
The section of code that is applied to my error is

**********************************************
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
***********************************************

Why would I be getting this error?

Any ideas?

Thanks

This is a section from my verbose log.....

*******************************************************************
'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

'retrieve addin list
addinListArray = split(addinList,",")

MSI (s) (EC:9C) [11:23:42:411]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: 12CF348, pcchPathBuf: 12CF344, pcchArgsOffset: 12CF29C
MSI (s) (EC:9C) [11:23:42:411]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value
MSI (s) (EC:9C) [11:23:42:411]: MsiProvideComponentFromDescriptor is returning: 0
MSI (s) (EC:58) [11:23:43:849]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: F9ED44, pcchPathBuf: F9ED40, pcchArgsOffset: F9EC98
MSI (s) (EC:58) [11:23:43:849]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value
MSI (s) (EC:58) [11:23:43:849]: MsiProvideComponentFromDescriptor is returning: 0
AddExcelAddinStatus: Deferred property contains: C:\Hyperion\products\Essbase\EssbaseClient\bin\essexcln.xll,C:\Hyperion\products\Essbase\EssbaseClient\bin\essxleqd.xla
MSI (s) (EC:3C) [11:23:45:177]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: D5BED44, pcchPathBuf: D5BED40, pcchArgsOffset: D5BEC98
MSI (s) (EC:3C) [11:23:45:177]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value
MSI (s) (EC:3C) [11:23:45:177]: MsiProvideComponentFromDescriptor is returning: 0
AddExcelAddinStatus: Created Excel object.
AddExcelAddinStatus: Created worksheet object.
AddExcelAddinStatus: Error: Unable to get the Add property of the AddIns class
Failed to add add-in 'C:\Hyperion\products\Essbase\EssbaseClient\bin\essexcln.xll'.
'C:\Hyperion\products\Essbase\EssbaseClient\bin\essexcln.xll' was not installed.
MSI (s) (EC:E8) [11:23:47:302]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: 67ED44, pcchPathBuf: 67ED40, pcchArgsOffset: 67EC98
MSI (s) (EC:E8) [11:23:47:302]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value
MSI (s) (EC:E8) [11:23:47:302]: MsiProvideComponentFromDescriptor is returning: 0
AddExcelAddinStatus: Unable to install Excel add-in 'C:\Hyperion\products\Essbase\EssbaseClient\bin\essexcln.xll'
MSI (s) (EC:E8) [11:23:47:552]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: 67ED44, pcchPathBuf: 67ED40, pcchArgsOffset: 67EC98
MSI (s) (EC:E8) [11:23:47:552]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value
MSI (s) (EC:E8) [11:23:47:552]: MsiProvideComponentFromDescriptor is returning: 0
AddExcelAddinStatus: Created Excel object.
MSI (s) (EC:3C) [11:32:44:831]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: D5BED44, pcchPathBuf: D5BED40, pcchArgsOffset: D5BEC98
MSI (s) (EC:3C) [11:32:44:831]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value
MSI (s) (EC:3C) [11:32:44:831]: MsiProvideComponentFromDescriptor is returning: 0
AddExcelAddinStatus: Created worksheet object.
MSI (s) (EC:3C) [11:32:45:690]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: D5BED44, pcchPathBuf: D5BED40, pcchArgsOffset: D5BEC98
MSI (s) (EC:3C) [11:32:45:690]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value
MSI (s) (EC:3C) [11:32:45:690]: MsiProvideComponentFromDescriptor is returning: 0
AddExcelAddinStatus: Add-in 'C:\Hyperion\products\Essbase\EssbaseClient\bin\essxleqd.xla' installed successfully.

****************************************************************

AddExcelAddinStatus: Created Excel object.
AddExcelAddinStatus: Created worksheet object.
AddExcelAddinStatus: Error: Unable to get the Add property of the AddIns class Weird! Especially as a subsequent add-in gets added successfully.

In your position, I'd record a macro adding the add-in which failed and see if you get the same error. As a precursor, though, try adding a few 'Err.Clear' statements above any code whose execution is tested with an 'If Err.Number <> 0 Then' test. I'm just wondering if an earlier error is somehow filtering through...

Also, please try and remember to use the CODE tag when posting lengthy text.

Hmmm. Make sure you've loaded Excel for the first time, and got any self-healing out of the way. Then install your MSI.

If that doesnt work, I'm wondering if

.AddIns.Add(strAddIn)

fails, is it worth trying

.RegisterXLL(strAddIn)

instead?

I'm honestly not so sure unless I can test it with your XLL. I only had a limited number of addins to write this code on, so I can't imagine every single addin is catered for.....yet.

Hi, I’m not sure if this will help but….. When I install the msi the very first time I get the Runtime Error 53. If I then open Excel and then close Excel, Uninstall my msi. And then reinstall my msi, it installs ok. Please find the latest log below. However, even though the addin has been installed I am getting errors in the log – AddExcelAddinStatus: Unable to install Excel add-in 'C:\Hyperion\products\Essbase\EssbaseClient\bin\essexcln.xll' AddExcelAddinStatus: Unable to install Excel add-in 'C:\Hyperion\products\Essbase\EssbaseClient\bin\essxleqd.xla'
Code below.........


MSI (s) (B4:88) [16:34:58:419]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: B8F348, pcchPathBuf: B8F344, pcchArgsOffset: B8F29C MSI (s) (B4:88) [16:34:58:419]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value MSI (s) (B4:88) [16:34:58:419]: MsiProvideComponentFromDescriptor is returning: 0 MSI (s) (B4:2C) [16:34:58:654]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: A0ED44, pcchPathBuf: A0ED40, pcchArgsOffset: A0EC98 MSI (s) (B4:2C) [16:34:58:654]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value MSI (s) (B4:2C) [16:34:58:654]: MsiProvideComponentFromDescriptor is returning: 0 AddExcelAddinStatus: Deferred property contains: C:\Hyperion\products\Essbase\EssbaseClient\bin\essexcln.xll,C:\Hyperion\products\Essbase\EssbaseClient\bin\essxleqd.xla MSI (s) (B4:2C) [16:34:58:888]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: A0ED44, pcchPathBuf: A0ED40, pcchArgsOffset: A0EC98 MSI (s) (B4:2C) [16:34:58:888]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value MSI (s) (B4:2C) [16:34:58:888]: MsiProvideComponentFromDescriptor is returning: 0 AddExcelAddinStatus: Created Excel object. MSI (s) (B4:2C) [16:35:03:138]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: A0ED44, pcchPathBuf: A0ED40, pcchArgsOffset: A0EC98 MSI (s) (B4:2C) [16:35:03:138]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value MSI (s) (B4:2C) [16:35:03:138]: MsiProvideComponentFromDescriptor is returning: 0 AddExcelAddinStatus: Created worksheet object. AddExcelAddinStatus: Add-in 'C:\Hyperion\products\Essbase\EssbaseClient\bin\essexcln.xll' installed successfully. MSI (s) (B4:00) [16:35:03:559]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: 67ED44, pcchPathBuf: 67ED40, pcchArgsOffset: 67EC98 MSI (s) (B4:00) [16:35:03:559]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value MSI (s) (B4:00) [16:35:03:559]: MsiProvideComponentFromDescriptor is returning: 0 AddExcelAddinStatus: Unable to install Excel add-in 'C:\Hyperion\products\Essbase\EssbaseClient\bin\essexcln.xll' AddExcelAddinStatus: Created Excel object. MSI (s) (B4:00) [16:35:03:934]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: 67ED44, pcchPathBuf: 67ED40, pcchArgsOffset: 67EC98 MSI (s) (B4:00) [16:35:03:934]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value MSI (s) (B4:00) [16:35:03:934]: MsiProvideComponentFromDescriptor is returning: 0 AddExcelAddinStatus: Created worksheet object. MSI (s) (B4:00) [16:35:04:091]: Entering MsiProvideComponentFromDescriptor. Descriptor: T%i&7oT-C?kaTW(0fqX8Toolbox>M5KDYSUnf(HA*L[xeX)y, PathBuf: 67ED44, pcchPathBuf: 67ED40, pcchArgsOffset: 67EC98 MSI (s) (B4:00) [16:35:04:091]: MsiProvideComponentFromDescriptor called for component {997FA962-E067-11D1-9396-00A0C90F27F9}: returning harcoded oleaut32.dll value MSI (s) (B4:00) [16:35:04:091]: MsiProvideComponentFromDescriptor is returning: 0 AddExcelAddinStatus: Add-in 'C:\Hyperion\products\Essbase\EssbaseClient\bin\essxleqd.xla' installed successfully. MSI (s) (B4:38) [16:35:04:137]: Executing op: ActionStart(Name=WriteIniValues,Description=Writing INI file values...,Template=File: [1] Section: [2] Key: [3], Value: [4]) AddExcelAddinStatus: Unable to install Excel add-in 'C:\Hyperion\products\Essbase\EssbaseClient\bin\essxleqd.xla'


Mark,

From doing a quick test with the XLL you sent me, mine did the same as yours. When i try to add it manually (attempted to record it in a macro) ProcMon tells me that the addin depends on ESSCSLN.dll (and probably a few more too.....). So I got exactly the same error doing it manually.

Since I dont have all the required DLL dependencies, what i'd suggest first is to ensure your sequencing is exactly as described above. That ensures all relevant ProgIds/CLSIDs are registered before it attempts to add the addin. And also, does Essbase write an environment variable? Appending to the PATH env var maybe? I have no idea, but if it does it may be worth sequencing the 'installAddin' CA after 'WriteEnvironmentStrings' instead.....

Try what I've said and let me know how it goes.....

Hi,

My msi does have Environment variables in it and I've moved the InstallAddin Custom Action after the 'WriteEnvironmentStrings' CA.
It still didn't work.

I'vew moved the CA all the way down to After ScheduleReboot but still getting the same issue.
I'm baffled as to why it works the second time round. i.e It installs with the Runtime Error 53 then I uninstall Essbase msi and then reinstall it and it installs ok.

Like I said before......baffled!
I wonder if my dependent Dll's are not registering properly. I will keep you posted on any progress I make.

Thanks for your help.
Mark

I'm baffled as to why it works the second time round - probably because what i mentioned above. If you've never run Office before, when the 'installAddin' gets to the line:

Set objXL = CreateObject("Excel.Application")

It will most probably initiate some Self-healing, because 'Excel.Application' is a ProgId in the Office app. That's why i said you need to have loaded Excel manually FIRST, then close it, before you install your MSI.....

All you need to do then, Cap'n, is alter the script so that it:

- tests for the 'FirstRun' entry in HKCU\Software\Microsoft\Office\[any number from 8 to 12]\Excel\Options
- if a non-zero value is found or the value is absent
- start Excel
- create a dummy worksheet
- delete it
- close Excel
- start the add-in installation.

You have one hour. Go!

You have one hour. Go! - you sound just like my line manager....[;)]

Hi,

Sorry, I didn't mention that before.
I have been previously running Excel before installing my msi so that it repairs.

I have made some progress but still have to figure out what it is.

I have created a snapshot of the following.....

Run Excel.
Install msi - I get error messages (Runtime Error '53')
Run Excel - check for addins, the xla file is added but not the other.
Close Excel
Uninstall my msi

I am just about to start delving into that to see if there's something in there that's fixing my issue as
after this when I install my msi again it installs without error and the addins appear.

Will keep you posted....

17minutes left Captain planet ;-) Tick Tock......
After thi

.....geez. The pressure on these forums is immense....[;)]

I've updated the code above ('setAddinProperty' and 'installAddin' CAs), but I've not tested it properly as my VMs have gone caput at the moment. I think it's VMWare's way of telling me i shouldn't be working, and i should be heading down to the pub....

....I'd hold fire on this one - i need to do more testing....

I'm WAITING!

Captain Planet,

You are nothing short of a legend.

Will test them out and let you know how I get on!
Go get yourself a beer!

Hmmm.....this is going to need more thought. I've detected if office has been run for the first time (or not), and initiated the Excel heal by creating a 'Excel.Application' if it's not been run. But obviously the healing is done in a separate process, and the CAs dont wait. So the addin probably tries to get added whilst Excel is healing.

Ahhh. Then i thought about doing a shell.run (with wait set to true) and repairing the HKCU registry of the office app, but then i'd need to detect the relevant Office product code, and then.....ahhhh. Enough's enough for one week.....

Hi,

I've narrowed down my problem to the Environment variables.
If the following system environment variables don't exist before the install then I get the Runtime error 53.

I ran a reg key that added the following to Path Environment variable.

C:\Hyperion\products\Essbase\EssbaseClient;
C:\Hyperion\products\Essbase\EssbaseClient\bin;
C:\Hyperion\common\ODBC\Merant\5.2\Drivers;

Then my install works without error.

I'm confused as I have sequenced my InstallAddin Custom Action after the WriteEnvironmentStrings Custom Action.
I thought this would ensure that the Environment variables were installed prior to my CA being run.

As the InstallAddin CA is run as a deferred CA in User Context would this cause the CA to be run before or after the WriteEnvironmentStrings CA?
If I set the InstallAddin CA to Immediate Execution what happens? I will give it a go.

Rgds,
Mark

Remember: changes or additions to user-level environment variables are "active" to processes created AFTER the change is made whereas system-level changes are available only after a restart UNLESS a special, system-wide Windows "message" is broadcast (as happens when using the Control Panel 'System' applet). Guess what? Windows Installer does not broadcast that message. Shock...

Good work tracking down your issue, BTW.

Hmmmm. Yeah....the SDK does quote (i know VBScab hates me quoting the SDK, but here i go again....):

".....environment variables do not change for the installation in progress when either the WriteEnvironmentStrings action or RemoveEnvironmentStrings action are run. On Windows NT and Windows 2000, this information is stored in the registry and a message is sent to notify the system of changes when the installation completes." - which is too late for us.

I guess the script could read the Environment table in the session and set the path from there....but then again this may create a duplicate in the path when the installation completes. Let me have a think....

Cap'n, I think editing the script to add paths etc. would make it too specific to this add-in. There will ALWAYS be shenanagins to be undertaken of one kid or another with different apps. I'd recommend leaving the script as it is and letting members shoe-horn in whatever other solutions their particular scenario requires.

And yes, that's what the SDK *says*...Meanwhile, in the real world...LOL

EDIT:
I'm pretty sure I sanity-tested my assertion some time back by creating a simple package which updated the System path and that the only time the new variable became visible was after a restart, whereas switching it to a User path made it visible to all subsequent processes. If someone wants to experiment further and report back, BMG.

Cap'n, I think editing the script to add paths etc. would make it too specific to this add-in. - i see what you're saying. But then again, if doin gthat would make it a more 'complete' solution then it could be worthwhile.....

Anyhow, for the record, the 'first run' self-healing issue should be fixed, and I've updated the script above.

Hi captain_planet,

Using the old scripts.....
I've managed to get my install working.....BUT.....

The only way I've been able to figure out how to do it is to create another msi which just contains the environment variables in question.

If I install this msi and then run my Essbase msi it installs fine. No Error!

Also,
I copied your edited script above into my Custom Actions and have just tried installing my msi.
However, I'm getting a Windows Installer error 1720 when trying to install my msi.

The error message is as follows.....

MSI (s) (30:88) [11:43:19:568]: Product: Oracle_HyperionEssbaseClient_11.1.1.3 -- Error 1720. There is a problem with this Windows Installer package. A script required for this install to complete could not be run. Contact your support personnel or package vendor. Custom action installAddin script error -2146828279, Microsoft VBScript runtime error: Subscript out of range: 'keyCount' Line 136, Column 6,

Rgds,
Mark

I'm hoping you guys can help me with my next issue.

The Essbase msi that I am installing is the latest version of the software and I am having to install it on machines that have the older plugins installed.
I can remove the old plugins by uninstalling the old msi however the addins were not handled correctly in this msi.
Therefore when I remove the old msi it doesn't remove the OPEN keys from the registry. i.e the HKCU\Software\Microsoft\Office\11.0\Excel\Options\OPEN

When the user then logs in to that machine, as a result of those keys not getting removed, they get an error message saying it cannot access the old addin.

I guess if I tailor that script that you supplied I can remove these redundant keys?
I will attempt this and let you know how i get on but in the meantime, has anyone encountered this before?

Rgds,
Mark

WHOA! You need to be REALLY careful here, since each user's OPENx entry might be for a completely different add-in and your script hoofing it out might be - as I like to say - sub-optimal. For example, User1 might have it as OPEN (i.e. the very first add-in entry) and User2 might have it as OPEN6.

Any such script needs to be extremely robust in identifying the correct add-in before its removal.

Hi Captain Planet,

I'm having some problems with the scripts when using the RemoveAddin script to be prescise.

I have edited the script slightly to output error codes and error descriptions and also instead of it reading the Add-In values from the msi I have added these manually to the script.

However, it is not managaing to remove the addins successfully. I am receiving a couple of Error 13, Type Mismatch error messages at the point in the script where it is trying to delete the add-in -


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
If bTest Then Msgbox strMsg
'writeToLog(strMsg)
'If its the addin, we delete it
objRegistry.DeleteValue HIVEKEY,strAddinKeyPath,strAddinValueName

If Err.Number = 0 Then
strMsg = "Deleted: " & strAddinKeyPath & "\" & strAddinValueName
If bTest Then Msgbox strMsg
'writeToLog(strMsg)
Else
strMsg = "Could not delete: " & strAddinKeyPath & "\" & strAddinValueName
If bTest Then MsgBox strMsg & vbnewline & "Error Number:" & Err.Number & vbnewline & "Error Description:" & Err.Description
'writeToLog(strMsg)
'reset error handling
On Error GoTo 0
End If

End If
Next
End If


What's happening is that this is writing a value to the registry key -
HKCU\Software\Microsoft\Office\11.0\Excel\Add-in Manager\C:\Program Files\Hyperion\Essbase\Bin\essxleqd.xla

and then this value is not being deleted.

The full test of code that I am using is as follows -


'*************************************
'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}')
'*************************************
Dim bTest : bTest = True
'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 = True

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 = "C:\Program Files\Hyperion\Essbase\Bin\essxleqd.xla, C:\Program Files\Hyperion\Essbase\Bin\essexcln.xll"

'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 & "'"
If bTest Then Msgbox strMsg
'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."
If bTest Then Msgbox strMsg
'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
If bTest Then Msgbox strMsg
'writeToLog(strMsg)
Else
strMsg = "Attempting to remove Add-in for current user"
If bTest Then Msgbox strMsg
'writeToLog(strMsg)
End If

'unload and delete from add-in list for Office 97 to 2007
For officeversion = 8 to 12
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
If bTest Then Msgbox strMsg
'writeToLog(strMsg)
'If it is, we delete it
objRegistry.DeleteValue HIVEKEY,strOpenKeyPath,strOpenValueName
If bTest Then Msgbox Err.Number
If Err.Number = 0 Then
strMsg = "Unloaded: " & strOpenKeyPath & "\" & strOpenValueName
If bTest Then Msgbox strMsg
'writeToLog(strMsg)
Else
strMsg = "Could not unload: " & strOpenKeyPath & "\" & strOpenValueName
If bTest Then MsgBox strMsg & vbnewline & "Error Number:" & Err.Number & vbnewline & "Error Description:" & Err.Description
'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
If bTest Then Msgbox strMsg
'writeToLog(strMsg)
'If its the addin, we delete it
objRegistry.DeleteValue HIVEKEY,strAddinKeyPath,strAddinValueName

If Err.Number = 0 Then
strMsg = "Deleted: " & strAddinKeyPath & "\" & strAddinValueName
If bTest Then Msgbox strMsg
'writeToLog(strMsg)
Else
strMsg = "Could not delete: " & strAddinKeyPath & "\" & strAddinValueName
If bTest Then MsgBox strMsg & vbnewline & "Error Number:" & Err.Number & vbnewline & "Error Description:" & Err.Description
'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."
If bTest Then MsgBox strMsg & vbnewline & "Error Number:" & Err.Number & vbnewline & "Error Description:" & Err.Description
'writeToLog(strMsg)
Else
strMsg = "Created Excel object."
If bTest Then Msgbox strMsg
'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."
If bTest Then MsgBox strMsg & vbnewline & "Error Number:" & Err.Number & vbnewline & "Error Description:" & Err.Description
'writeToLog(strMsg)

'reset error handling
On Error GoTo 0
Else
strMsg = "Add-in '" & strAddIn & "' removed successfully."
blnInstalled = False
RemoveExcelAddin = True
If bTest Then Msgbox strMsg
'writeToLog(strMsg)
End If
Else
strMsg = "Add-in '" & strAddIn & "' is not installed, so no removal necessary." & vbCRLF & "'" & strAddIn & "' was not removed."
If bTest Then Msgbox strMsg
'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


Rgds,
Mark

False Alarm.....
I know what it is.....ignore my last post!

I've put in a space in the addinlist

addinList = "C:\Program Files\Hyperion\Essbase\Bin\essxleqd.xla, C:\Program Files\Hyperion\Essbase\Bin\essexcln.xll"

To ensure this doesn't cause an issue in future if running the script outside of the msi I've added Trim to the front of the addinListArray(i)

strAddInName = Trim(addinListArray(i))

This ensure that spaces or not in my addinList they will be removed.

Sorry about the false alarm!

Rgds,
Mark

For good measure, why not add a test for leading and trailing commas?

Hi Captain Planet,

I've tested your new scripts and the installaddin script doesn't want to install more than one addin it seems.

Also, your older scripts install ok without Excel being run first.

Rgds,
Mark

I'd be interested to know how XL handles the scenario where the 'OPENx' sequence for the add-in manager is non-sequential. That is, if there are 6 add-ins and one of the ones deleted by the script is in position 4 (OPEN3), we're left with the structure OPEN, OPEN1, OPEN2, OPEN4, OPEN5. My question is, are the other 5 add-ins listed in XL when it's restarted?

Mark, what does the log tell you? The good captain was kind enough to add lines writing each stage to that log so you should be able to tell what happened.

Sorry for not getting back, guys. Just been stacked with other stuff.

Mark - I've fixed the code so that it does multiple add-ins. I made a glitch with rushing - sorry.
VBScab - the code already checked and removed trailing commas so I've just used the trim function as well, just to be sure.

I've also just lumped 3 random addins into an example, installed them, and removed the middle 'OPEN' key and Excel (2007) seemed to handle it fine.

Thanks both.

Hi Captain,

Just wondering, what change was made to the code to make it do multiple add-ins?
Also, is it the InstallAddin section you changed only?

Is it by moving this section?


If Not blnFoundFirstRun Then
'resets registry keys so Excel heals agian on first launch
revertRegistry()
End If


I'm just trying to see the change you made before testing it.....

Thanks in advance

yep, pretty much....[;)] It was in the wrong place....

Hi captain planet,

Is there anything in this code that would prevent it from running under the system account through SCCM?

I'm having trouble when deploying through SCCM.

Rgds,
Mark

Sorry, Mark. I don't use SCCM so I have no idea. Are there no logs generated which can help you out?

SCCM logs events like no other app I've encountered.

@Mark: I suspect your problem is that Excel add-ins are installed for the user rather than the machine, so you will probably want to use Active Setup to run the script. As ever, you can add the logging argument to the package's command line in SCCM. Use %SystemRoot%\TEMP rather than %TEMP%.

OK, I have good news and bad news.

The good news is that I've discovered a solution to the environment variable issue. VBScab rightly pointed out an issue/feature of Windows Installer and system environment variables meaning that a reboot is required before changes take effect.

He also touched on the fact that environment variable changes (user or system in my experience) are only available to processes which are started AFTER the process which made the environment changes. This wasn't very useful to our automation CAs which depended on an environment change specified in the Environment table!

We could originally assume that sequencing our installAddin CA after the 'WriteEnvironmentStrings' standard action would mean that our 'new' environment changes (made via the Environment table) were made available to our addin automation script. This assumption would be incorrect.

Our automation script kicks off an 'Excel.exe' process to add the addins. If we pause things here, and go to 'System Properties > Environment Variables...' on your OS, you'll see that the environment changes ARE indeed present. However, open a command prompt and type 'SET' and you'll see that they are NOT present.

So we needed to ensure that the environment updates were available to the current installer session and the current process. I did this by scanning the current Installer session's Environment table for changes to the 'Path' environment variable, used a regular expression to retrieve the 'proper' path (ie 'C:\whatever', not '[WindowsVolume]whatever'), used the Windows Installer API (and the 'Environment' property of the Installer object - which I didn't know existed until I started digging) and WScript.Shell to change the environment for the current installer session and process.

Later on I also use the WScript.Shell to create, and then delete, an arbitrary SYSTEM environment variable. What this did was, as VBScab puts it, sends a "system-wide Windows "message"" which updates the environment variables on the target system and eliminates the requirement for a reboot.

Enough of this babble. Check the code out for yourself. ;-)

The bad news is, from an SCCM perspective, I've tested this and it won't work as it's installing from a system account. [:(]

love your work captain_planet :-)

Thanks for your comment, John. [;)] And thanks for your blog too - it's still one of my favourite reference points.....

Correct me if I'm wrong but I'm new to creating packages of any kind so I have some questions. When you talk about Windows Installer do you mean creating one out of Visual Studio or another program? I've got a single 3rd party .xla that I have to push out to all of our lab machines.

Thanks