How to handle Open Key Excel Add-ins via PSADT?

How to handle Open Key Excel Add-ins via PSADT?

HKCU\Software\Microsoft\Office\14.0\Excel\Options\OPEN[i]

Open1, Open2 , Open3 etc based on count present in machine, script has to check count of Open and assign Open[i] and write value for it.

Please suggest. Thank You

Infact I was looking for a way to do this effectively. Au suggestions on this?

We eventually punted and are using a VBS script which works fine. Frustrating though as I’m sure this can be done using PS . If your interested I can post the VBS. It ain’t PS but it works :slight_smile:

Does not seem that complex to write. Is it HKCU only or HKLM also?

We use this code to accomplish this. Simple but it does the job.

## Function to Add an addon to Excel
## Example: Add-ExcelAddonOffice2016-SW -XlamFileSourcePath "$dirfiles\signal.xlam" -XlamFileTargetPath "$envProgramFilesx86\Microsoft Office\root\Office16\Library\Signal.xlam"
Function Add-ExcelAddonOffice2016 {
	[CmdletBinding()]
	param(
	[Parameter(Mandatory=$True)]
	[String]$XlamFileSourcePath = "",
	[Parameter(Mandatory=$True)]
	[String]$XlamFileTargetPath = ""
    )
    $XlamName = Split-Path -Path $XlamFileSourcePath -Leaf
    Copy-File -Path $XlamFileSourcePath -Destination $XlamFileTargetPath #"$envProgramFilesx86\Microsoft Office\root\Office16\Library\Signal.xlam"

[Scriptblock]$InvokeXlam = {
    if ((Get-RegistryKey -Key "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\Options" -SID $UserProfile.SID) -and ($XlamName)) {
        $cnt = 0
        $OpenExist = $True
        $XlamOpenexist = $null
        While ($OpenExist) {
            if ($cnt -eq 0) {$OpenExist = Get-RegistryKey -Key "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\Options" -Value "OPEN" -SID $UserProfile.SID}
            else {$OpenExist = Get-RegistryKey -Key "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\Options" -Value "OPEN$cnt" -SID $UserProfile.SID}
            if ($OpenExist) {
                $cnt++
                if ($openexist.ToLower() -Match $XlamName) {$XlamOpenexist = $True}
            }
        }
        if (!($XlamOpenexist) -and ($cnt -eq 0)) {Set-RegistryKey -Key "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\Options" -Name "OPEN" -Value "`"$XlamFileTargetPath`"" -Type String -SID $UserProfile.SID}
        if (!($XlamOpenexist) -and ($cnt -gt 0)) {Set-RegistryKey -Key "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\Options" -Name "OPEN$cnt" -Value "`"$XlamFileTargetPath`"" -Type String -SID $UserProfile.SID}
    }
}
Invoke-HKCURegistrySettingsForAllUsers -RegistrySettings $InvokeXlam
}

1 Like

R. Robesin - Thanks. This looks like it should work :+1: We’ll give that a try. Will be happy to be finally able to decom the call out from our PS Wrapper to the ole VBS

We just encountered the situation that we also had to remove an addin from Excel. This is the quick code we wrote for it.

        $XlamFile = "filename.xlam"
        [Scriptblock]$RevokeXlam = {
        for ($i=0; $i -le 10; $i++) {
            if ($i -eq 0) {$openvalue = Get-RegistryKey -Key "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options" -Value "OPEN" -SID $UserProfile.SID}
            else {$openvalue = Get-RegistryKey -Key "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options" -Value "OPEN$i" -SID $UserProfile.SID}
                if ($openvalue) {
                    if ($i -eq 0) {
                        $lastopen = "OPEN"
                        $lastvalue = $openvalue
                    }
                    if ($i -ne 0) {
                        $lastopen = "OPEN$i"
                        $lastvalue = $openvalue
                    }
                    if ($openvalue.tolower() -match $XlamFile.ToLower() -and $i -eq 0) {
                        Remove-RegistryKey -Key "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options" -Name "OPEN" -SID $UserProfile.SID
                        $remopen = "OPEN"
                    }
                    if ($openvalue.tolower() -match $XlamFile.ToLower() -and $i -ne 0) {
                        Remove-RegistryKey -Key "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options" -Name "OPEN$i" -SID $UserProfile.SID
                        $remopen = "OPEN$i"
                    }
                }
            }
            if ($remopen -and $lastopen) {
                if ($remopen -ne $lastopen) {
                    Set-RegistryKey -Key "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options" -Name $remopen -Value $lastvalue -SID $UserProfile.SID
                    Remove-RegistryKey -Key "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options" -Name $lastopen -SID $UserProfile.SID
                }
            }
        }

        Invoke-HKCURegistrySettingsForAllUsers -RegistrySettings $RevokeXlam
1 Like

This is amazing, Thank you @R.Robesin Will give it shot and see if this is serving the purpose. No matter what this is gonna go to my personal repo.

@luki1412
Can I submit a Feature Request for this? Or do you think its not worth it to be placed in there as only used for Excel plug-ins?

Hi @R.Robesin,
Thanks for the script. The script will add current user setting to existing logged in user on the device. Will it work when new user logs in as well?

Well, the toolkit manual contains for the following for the command Invoke-HKCURegistrySettingsForAllUsers.
Set current user registry settings for all current users and any new users in the future.
So it should also work for new people logging onto the machine.