Last active
June 24, 2021 16:44
-
-
Save Skatterbrainz/36c66c6fac1c1ef430d57c4b1a46c3bd to your computer and use it in GitHub Desktop.
Find and Replace text strings in Excel workbooks
This file contains 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 | |
Find and Replace text a string pattern within XLSX files in a given location | |
.DESCRIPTION | |
Search all XLSX workbook files for matching text string and replace with a new text string. | |
.PARAMETER Path | |
Path (folder) where .XLSX files reside | |
.PARAMETER SearchFor | |
Text string to search for | |
.PARAMETER ReplaceWith | |
Text string to insert in place of [SearchPattern] text string | |
.PARAMETER LogFile | |
CSV output path+filename | |
.PARAMETER NoRecurse | |
Scan xlsx files in Path location only, do not scan into subfolders | |
.PARAMETER Apply | |
Confirm saving changes to workbooks | |
.EXAMPLE | |
.\Replace-ExcelWorkbookText.ps1 -Path "\\server123\docs" -SearchFor "Server543" -ReplaceWith "Server9022" -Apply | |
Replaces instances of "Server543" with "Server9022" within any cells on any sheets of workbooks in | |
the path \\server123\docs. Saves changes back to workbooks. | |
.EXAMPLE | |
.\Replace-ExcelWorkbookText.ps1 -Path "\\server123\docs" -SearchFor "Server543" -ReplaceWith "Server9022" -NoRecurse -Apply | |
Searches for instances of "Server543" within any cells on any sheets of workbooks in | |
the path \\server123\docs, but no subfolders, and save to CSV file. Saves changes back to workbooks. | |
.EXAMPLE | |
$results = .\Replace-ExcelWorkbookText.ps1 -Path "\\server123\docs" -SearchFor "Server543" -ReplaceWith "Server9022" | |
$results | Group-Object Workbook | Select-Object Count,Name | |
Returns counts of matching instances by workbook filename | |
.NOTES | |
1.0 - 2021-06-17 - David Stein or his genetic clone | |
# THIS CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, | |
# INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THIS | |
# INCLUDES EPISODES OF MILD TO EXTREME INTOXICATION AND DERELICTION OF MENTAL STABILITY OR DIAGNOSED INSANITY. | |
# I/We/They/He/She/It grants You a nonexclusive, royalty-free right to use and modify, but not distribute, the code, | |
# provided that You (yes, You) agree: | |
# (i) to retain Our name, logo, or trademarks referencing Us as the original provider of the code; | |
# (ii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including | |
# attorney fees, that arise or result from the use of the Code. Attorney fees, ha ha ha! That's a good one! | |
#> | |
[CmdletBinding()] | |
param ( | |
[parameter(Mandatory=$False)][string]$Path = 'C:\temp', | |
[parameter(Mandatory=$False)][string]$SearchFor = 'Mike Hunt', | |
[parameter(Mandatory=$False)][string]$ReplaceWith = 'Sales Manager', | |
[parameter(Mandatory=$False)][switch]$Apply | |
) | |
[string]$LogFile = "$($env:USERPROFILE)\desktop\excel_replace_$(Get-Date -f 'yyyyMMddhhmm').csv" | |
if ([string]::IsNullOrEmpty($Path) -or [string]::IsNullOrEmpty($SearchFor)) { | |
Write-Warning "Path and SearchFor inputs cannot be empty" | |
break | |
} | |
if (!(Test-Path $Path)) { | |
Write-Warning "Path not found: $Path" | |
break | |
} | |
function Release-ComObject($ref) { | |
while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref) -gt 0) {} | |
[void][System.GC]::Collect() | |
} | |
try { | |
[array]$files = Get-ChildItem -Path $Path -Filter "*.xlsx" -ErrorAction Stop | |
Write-Host "$($files.Count) workbook files were found" | |
} | |
catch { | |
Write-Warning "Unable to retrieve any workbook files from $Path" | |
} | |
Write-Verbose "Opening excel application session" | |
$excel = New-Object -ComObject Excel.Application | |
$excel.Visible = $False | |
$logresults = @() | |
Write-Host "Begin processing" | |
foreach ($file in $files) { | |
Write-Host "file: $($file.FullName)" | |
$Workbook = $excel.Workbooks.Open($file.FullName) | |
$worksheets = $workbook.Worksheets | |
foreach ($worksheet in $worksheets) { | |
Write-Verbose "worksheet: $($worksheet.Name)" | |
$found = $worksheet.UsedRange.Find($SearchFor) | |
if ($null -ne $found) { | |
Write-Verbose "match was found" | |
$address1 = $found.Address(0,0,1,1) | |
$row = $found.Row | |
$col = $found.Column | |
[string]$textvalue = $worksheet.cells($row,$col).Formula | |
[string]$newvalue = $textvalue.Replace($SearchFor,$ReplaceWith) | |
if ($textvalue -ne $newvalue) { | |
if ($Apply) { | |
$worksheet.Cells($row,$col).Formula = $newvalue | |
$logresults += ( | |
[PSCustomObject]@{ | |
File = $file.FullName | |
Worksheet = $($worksheet.Name) | |
Row = $row | |
Column = $col | |
OldText = $textvalue | |
NewText = $newvalue | |
} | |
) | |
} | |
} | |
while ($found = $worksheet.UsedRange.FindNext($found)) { | |
$address2 = $found.Address(0,0,1,1) | |
if ($address1 -eq $address2) { break } | |
$row = $found.Row | |
$col = $found.Column | |
[string]$textvalue = $worksheet.cells($row,$col).Formula | |
[string]$newvalue = $textvalue.Replace($SearchFor,$ReplaceWith) | |
if ($textvalue -ne $newvalue) { | |
if ($Apply) { | |
$worksheet.Cells($row,$col).Formula = $newvalue | |
$logresults += ( | |
[PSCustomObject]@{ | |
File = $file.FullName | |
Worksheet = $($worksheet.Name) | |
Row = $row | |
Column = $col | |
OldText = $textvalue | |
NewText = $newvalue | |
} | |
) | |
} | |
} | |
} # while | |
} else { | |
Write-Verbose "no matches found" | |
} | |
} # foreach worksheet | |
if ($Apply) { | |
if (!$workbook.Saved) { | |
Write-Verbose "saving changes to workbook" | |
$workbook.Save() | |
} | |
} | |
$workbook.Close($False) | |
} # foreach file | |
$excel.quit() | |
Write-Verbose "release: worksheet" | |
Release-ComObject $worksheet | |
Write-Verbose "release: worksheets" | |
Release-ComObject $worksheets | |
Write-Verbose "release: workbook" | |
Release-ComObject $workbook | |
Write-Verbose "terminating all excel.exe processes" | |
Get-Process 'excel' | Stop-Process -Force | |
if ($logresults.Count -gt 0) { | |
$logresults | Export-Csv -Path $LogFile -NoTypeInformation -Force | |
Write-Host "Complete! Change log saved to $LogFile" | |
} else { | |
Write-Host "Complete! No changes were applied" | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment