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
Sri
April 8, 2021, 4:10pm
2
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
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
}
2 Likes
R. Robesin - Thanks. This looks like it should work 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
2 Likes
Sri
May 7, 2021, 3:55pm
8
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.
Thank you for this. It looks great!
For my use, I will be including the following, if anyone else might find the same value.
$ComObject = New-Object -ComObject excel.application
$OfficeVersion = $ComObject.version
This would an example of where I will use it.
“HKCU\SOFTWARE\Microsoft\Office\$OfficeVersion\Excel\Options”
Great Work. Working fine as expected both add & remove.
Thank You So Much Robesin.
1 Like
system
Closed
October 18, 2022, 1:56pm
16
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.