Last active
March 9, 2022 13:31
-
-
Save rileyz/87786a986fa6cd6d4ec8d9c3e8576634 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <# | |
| .SYNOPSIS | |
| Script to assist with managing Office Add-ins via the OPENx Values in the HKCU registry. | |
| .DESCRIPTION | |
| Intended Use | |
| This script was produced to assist in adding or removing Office Add-ins. In the Office | |
| application, the settings dialog can be found in File > Options > Add-ins > | |
| Manage Add-ins > Go. | |
| In the registry, the OPENx Values can be found in the registry, for example. | |
| HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options | |
| For more information about the OPENx Values, please view the URL below. | |
| https://bettersolutions.com/excel/add-ins/registry-keys.htm | |
| About | |
| This script was created to solve the issue of adding or removing a OPENx numbered Values, | |
| because OPENx Values can be numbered and ordered differently for each user. | |
| Known Defects/Bugs | |
| * Bug where single quotes must be used for the parameter OfficeAddinsValueData, or double quotes | |
| within the value cannot be escaped. The registry value data normally uses double quotes for | |
| string encapsulation. | |
| Code Snippet Credits | |
| * https://github.com/rileyz/VMR-Stable/blob/master/Virtual%20Machine%20Runner/Framework/Core_CommonFunctions.ps1 | |
| * https://devblogs.microsoft.com/scripting/use-powershell-to-enumerate-registry-property-values/ | |
| Version History | |
| 1.00 2022-03-09 | |
| Initial release. | |
| 1.01 2022-03-09 | |
| Bug fix where if $OfficeOPENxArray was null, it would cause an error. | |
| Copyright & Intellectual Property | |
| Feel to copy, modify and redistribute, but please pay credit where it is due. | |
| Feed back is welcome, please contact me on LinkedIn. | |
| .LINK | |
| Author:.......https://www.linkedin.com/in/rileylim | |
| Source Code:..https://gist.github.com/rileyz/87786a986fa6cd6d4ec8d9c3e8576634 | |
| Article:......https://www | |
| .EXAMPLE | |
| Parameters example. | |
| -Mode Add|Remove | |
| Adding or removing the OPENx Value. | |
| -OfficeAddinsRegistryKey 'HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options' | |
| Path to HKCU Office application Options key. | |
| -OfficeAddinsValueData '"C:\Program Files (x86)\Contoso\Finance.xla"' | |
| Path to the Add-in | |
| .EXAMPLE | |
| To add a Excel Add-in to a user. | |
| Manage-OfficeAddins -Mode add -OfficeAddinsRegistryKey 'HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options' -OfficeAddinsValueData '"C:\Program Files (x86)\Contoso\Finance.xla"' | |
| .EXAMPLE | |
| To add a Excel Add-in to a user. | |
| Manage-OfficeAddins -Mode add -OfficeAddinsRegistryKey 'HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options' -OfficeAddinsValueData '/A "Contoso.PaySlip.Control"' | |
| .EXAMPLE | |
| To remove a Excel Add-in to a user. | |
| Manage-OfficeAddins -Mode add -OfficeAddinsRegistryKey 'HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options' -OfficeAddinsValueData '"C:\Program Files (x86)\Contoso\Finance.xla"' | |
| .EXAMPLE | |
| To remove a Excel Add-in to a user. | |
| Manage-OfficeAddins -Mode add -OfficeAddinsRegistryKey 'HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options' -OfficeAddinsValueData '/A "Contoso.PaySlip.Control"' | |
| #> | |
| # Function List ################################################################################### | |
| Function Get-RegistryKeyPropertiesAndValues | |
| { | |
| <# | |
| .Synopsis | |
| This function accepts a registry path and returns all reg key properties and values | |
| .Description | |
| This function returns registry key properies and values. | |
| .Example | |
| Get-RegistryKeyPropertiesAndValues -path ‘HKCU:\Volatile Environment’ | |
| Returns all of the registry property values under the \volatile environment key | |
| .Parameter path | |
| The path to the registry key | |
| .Notes | |
| NAME: Get-RegistryKeyPropertiesAndValues | |
| AUTHOR: ed wilson, msft | |
| LASTEDIT: 05/09/2012 15:18:41 | |
| KEYWORDS: Operating System, Registry, Scripting Techniques, Getting Started | |
| HSG: 5-11-12 | |
| .Link | |
| Http://www.ScriptingGuys.com/blog | |
| #Requires -Version 2.0 | |
| #> | |
| Param( | |
| [Parameter(Mandatory=$true)] | |
| [string]$path) | |
| Push-Location | |
| Set-Location -Path $path | |
| Get-Item . | | |
| Select-Object -ExpandProperty property | | |
| ForEach-Object { | |
| New-Object psobject -Property @{“property”=$_; | |
| "Value" = (Get-ItemProperty -Path . -Name $_).$_}} | |
| Pop-Location | |
| } #end function Get-RegistryKeyPropertiesAndValues | |
| Function Manage-OfficeAddins { | |
| Param ([Parameter(Mandatory=$true)][ValidateSet('Add','Remove')][string[]]$Mode, | |
| [Parameter(Mandatory=$true)][String]$OfficeAddinsRegistryKey, | |
| [Parameter(Mandatory=$true)][String]$OfficeAddinsValueData | |
| ) | |
| #Load raw array with all OPENx registry values and data. | |
| $RawArray = Get-RegistryKeyPropertiesAndValues "$OfficeAddinsRegistryKey" | where {$_.property -like 'OPEN*'} | |
| #Bash array to split the values to get just the number. | |
| $OfficeOPENxArray = @() | |
| $RawArray | foreach { | |
| Write-Debug "Property: $($_.property)" | |
| Write-Debug "Value: $($_.Value)" | |
| Write-Debug "OPENx: $(($_.property -split 'OPEN')[1])" | |
| $PSCustomObject = [PSCustomObject]@{ | |
| RegistryValueNameX = [int]($_.property -split 'OPEN')[1] | |
| RegistryValueData = $_.Value | |
| } | |
| $OfficeOPENxArray += $PSCustomObject | |
| } | |
| #Sorting to ensure ordering. | |
| $OfficeOPENxArray = $OfficeOPENxArray | sort -Property RegistryValueNameX | |
| Write-Debug $($OfficeOPENxArray | Out-String) | |
| #Working out the next free number for OPENx. | |
| if ($OfficeOPENxArray -eq $null) { | |
| $NextFreeOPENxNumber = '' | |
| } else { | |
| $NextFreeOPENxNumber = (($OfficeOPENxArray[$($OfficeOPENxArray.Count - 1)].RegistryValueNameX)) + 1 | |
| } | |
| Write-Debug "NextFreeOPENxNumber: $NextFreeOPENxNumber" | |
| Write-Verbose "Manage-OfficeAddins mode is $Mode." | |
| Switch ($Mode) { | |
| 'Add' {if (!($OfficeOPENxArray.RegistryValueData -contains $OfficeAddinsValueData)) { | |
| Write-Verbose " Addin not found, adding $('OPEN' + "$NextFreeOPENxNumber") Value and Data." | |
| $Result = Write-Registry -RegistryKey "$OfficeAddinsRegistryKey" -RegistryValueName $('OPEN' + "$NextFreeOPENxNumber") -RegistryValueData "$OfficeAddinsValueData" -RegistryValueType String | |
| Write-Debug "Write-Registry result $Result." | |
| } else { | |
| Write-Verbose ' Addin found, no action required.' | |
| } | |
| } | |
| 'Remove' {$OfficeOPENxArray | foreach { | |
| Write-Debug "RegistryValueNameX: $($_.RegistryValueNameX)" | |
| Write-Debug "RegistryValueData: $($_.RegistryValueData)" | |
| if ($_.RegistryValueData -contains "$OfficeAddinsValueData") { | |
| if ($($_.RegistryValueNameX) -eq 0) { | |
| $RemoveRegistryValue = 'OPEN' | |
| } else { | |
| $RemoveRegistryValue = $('OPEN' + $_.RegistryValueNameX) | |
| } | |
| Write-Verbose " Addin found, removing $RemoveRegistryValue Value and Data." | |
| Remove-ItemProperty -Path "$OfficeAddinsRegistryKey" -Name $RemoveRegistryValue | |
| } | |
| } | |
| } | |
| } | |
| } #End Function Manage-OfficeAddins | |
| Function Write-Registry { | |
| Param ([Parameter(Mandatory=$true)][String]$RegistryKey, | |
| [Parameter(Mandatory=$true)][String]$RegistryValueName, | |
| [String]$RegistryValueData, | |
| [Parameter(Mandatory=$true)][String]$RegistryValueType, | |
| [Switch]$EnableReflectiontoWOW3264Node) | |
| #Need to add logic to write to WOW3264Node if in 64bit system via switch. | |
| Try {Switch ((Get-ItemProperty -Path $RegistryKey -Name $RegistryValueName -ErrorAction SilentlyContinue).$RegistryValueName.gettype().name) | |
| {'String' {$RegistryValueTypeCheck = 'String'} | |
| 'Int32' {$RegistryValueTypeCheck = 'DWord'} | |
| 'Int64' {$RegistryValueTypeCheck = 'QWord'} | |
| 'String[]' {$RegistryValueTypeCheck = 'MultiString'} | |
| 'Byte[]' {$RegistryValueTypeCheck = 'Binary'} | |
| Default {Return 'Unable to discover registry type for overwrite check'}}} | |
| Catch {$RegistryValueTypeCheck = $null} | |
| If ($RegistryValueTypeCheck -ne $null) | |
| {If ($RegistryValueTypeCheck -ne $RegistryValueType) | |
| {Return 'Registry type mismatch'}} | |
| #Force create the registry path. | |
| If (((Test-Path $RegistryKey) -replace "`n|`r") -eq 'False') | |
| {$null = New-Item -Path $RegistryKey -Force} | |
| Switch ($RegistryValueTypeCheck) | |
| {MultiString {#Writing registry MultiString value. | |
| $MultiStringArray = Get-ItemProperty -Path $RegistryKey | Select-Object -ExpandProperty $RegistryValueName | |
| $MultiStringArray = @($MultiStringArray | where {$_ -ne $RegistryValueData}) | |
| If ($MultiStringArray -notcontains $RegistryValueData) | |
| {$MultiStringArray += $RegistryValueData} | |
| #Not in use yet, needs coded into params. | |
| If ($Remove -eq $true) | |
| {$MultiStringArray = @($MultiStringArray | where { $_ -ne $RegistryValueData })} | |
| Set-ItemProperty -Path $RegistryKey -type $RegistryValueTypeCheck -Name $RegistryValueName -Value $MultiStringArray | |
| Try {$RegistryWriteCheck = Get-ItemProperty -Path $RegistryKey | Select-Object -ExpandProperty $RegistryValueName | |
| If ($RegistryWriteCheck -contains $RegistryValueData) | |
| {$RegistryWriteCheck = $null | |
| Return '0'} | |
| Else{Return 'Unexpected value on validation'}} | |
| Catch {Return 'Error'}} | |
| Default {#Writing registry String, Dword, Qword, value. | |
| $null = New-ItemProperty -Path $RegistryKey -Name $RegistryValueName -Value $RegistryValueData -PropertyType $RegistryValueType -Force | |
| Try {$RegistryWriteCheck = (Get-ItemProperty -Path $RegistryKey | Select-Object -ExpandProperty $RegistryValueName) | |
| If ($RegistryWriteCheck -eq $RegistryValueData) | |
| {$RegistryWriteCheck = $null | |
| Return '0'} | |
| Else{Return 'Unexpected value on validation'}} | |
| Catch {Return 'Error'}}} | |
| #https://social.technet.microsoft.com/Forums/windowsserver/en-US/6ccf94ee-9a15-49fc-9abf-0050b66b0643/registry-how-to-test-the-type-of-a-value | |
| #http://blogs.technet.com/b/heyscriptingguy/archive/2015/04/02/update-or-add-registry-key-value-with-powershell.aspx | |
| #http://stackoverflow.com/questions/27238523/editing-a-multistring-array-for-a-registry-value-in-powershell | |
| #http://www.jonathanmedd.net/2014/02/testing-for-the-presence-of-a-registry-key-and-value.html | |
| #https://msdn.microsoft.com/en-us/library/microsoft.win32.registryvaluekind(v=vs.110).aspx | |
| } #End Function Write-Registry | |
| #<<< End Of Function List >>> | |
| # Setting up housekeeping ######################################################################### | |
| $DebugPreference = 'SilentlyContinue' #SilentlyContinue|Continue | |
| $VerbosePreference = 'SilentlyContinue' #SilentlyContinue|Continue | |
| #<<< End of Setting up housekeeping >>> | |
| # Start of script work ############################################################################ | |
| Manage-OfficeAddins -Mode add -OfficeAddinsRegistryKey 'HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options' -OfficeAddinsValueData '"C:\Program Files (x86)\Contoso\Finance.xla"' | |
| Manage-OfficeAddins -Mode add -OfficeAddinsRegistryKey 'HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options' -OfficeAddinsValueData '/A "Contoso.PaySlip.Control"' | |
| Manage-OfficeAddins -Mode Remove -OfficeAddinsRegistryKey 'HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options' -OfficeAddinsValueData '"C:\Program Files (x86)\Contoso\Finance.xla"' | |
| Manage-OfficeAddins -Mode Remove -OfficeAddinsRegistryKey 'HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\Options' -OfficeAddinsValueData '/A "Contoso.PaySlip.Control"' | |
| #<<< End of script work >>> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment