Scripting, Technology

Ridiculous Redactions


Today’s waste of time will focus on using PowerShell to crawl through a bunch of Microsoft Excel workbook files, commonly referred to as “Excel Database Files” by many MBA recipients, to effectively replace one text string pattern with another. A typical scenario might be that the workbook files contain hyperlinks to documents which have been moved to a new location, or maybe someone got fired, and the CEO hates them enough to demand their name be stricken from every workbook file.

For demonstration purposes let’s assume that CEO Jim Useless wants to replace every occurrence of the name of the former Sales Manager, Mike Hunt, with “Sales Manager”. Because, nothing makes a new employee feel valued and welcomed as much as being referred to only by their job title. So, an example source table (top example below) would be modified to look like the example below that.

Let’s drink a whole pot of coffee (or a whole box of Keurig cups) and get started.

Step 1 is setting up some variables to use later on.

[string]$SearchFor = "Mike Hunt"
[string]$ReplaceWith = "Sales Manager"

Step 2 is getting all the .xlsx files within the folder.

[array]$files = Get-ChildItem -Path $Path -Filter "*.xlsx" -ErrorAction Stop

Step 3 is making sure Excel is installed. I was hoping to use the ImportExcel module for this, but it doesn’t provide the functionality for doing this sort of thing yet, so we need to make sure Excel is available to handle the heavy-lifting.

try {
  Write-Verbose "Opening excel application session"
  $excel = New-Object -ComObject Excel.Application
  $excel.Visible = $False
}
catch {
  Write-Error "Excel could not be found on this cheap-ass computer."
  break
}

Step 4 is putting on some Latex gloves and facemask to begin crawling through all those stinky workbook files. Keep in mind that the Open() method may need to be modified to suit password-protected files, and other such silliness. Then fetch the worksheets within each workbook to start the search and replace fun.

foreach ($file in $files) {
  $workbook = $excel.Workbooks.Open($file.FullName)
  $worksheets = $workbook.Worksheets
  # add more stuff here to crawl through each worksheet in the workbook - see Step 5
}

Step 5 is searching for the text you want to replace. This will be shoved into the “add more stuff here” line in Step 4

# insert into while() loop for each workbook
$found = $worksheet.UsedRange.Find($SearchFor)
if ($null -ne $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) {
    $worksheet.Cells($row,$col).Formula = $newvalue
  }
  while ($found = $worksheet.UsedRange.FindNext($found)) {
    $address2 = $found.Address(0,0,1,1)
    if ($address1 -eq $address2) { break } # no more matches found on this worksheet.. skip to the next one
    $row = $found.Row
    $col = $found.Column
    [string]$textvalue = $worksheet.Cells($row,$col).Formula
    [string]$newvalue = $textvalue.Replace($SearchFor, $ReplaceWith)
    if ($textvalue -ne $newvalue) {
      $worksheet.Cells($row,$col).Formula = $newvalue
    }
  }
}
# end of code insertion

The final merged slurry of ingredients should look something like this…

foreach ($file in $files) {
  $workbook = $excel.Workbooks.Open($file.FullName)
  $worksheets = $workbook.Worksheets
  # insert into while() loop for each workbook
  $found = $worksheet.UsedRange.Find($SearchFor)
  if ($null -ne $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) {
      $worksheet.Cells($row,$col).Formula = $newvalue
    }
    while ($found = $worksheet.UsedRange.FindNext($found)) {
      $address2 = $found.Address(0,0,1,1)
      if ($address1 -eq $address2) { break } # no more matches found on this worksheet.. skip to the next one
      $row = $found.Row
      $col = $found.Column
      [string]$textvalue = $worksheet.Cells($row,$col).Formula
      [string]$newvalue = $textvalue.Replace($SearchFor, $ReplaceWith)
      if ($textvalue -ne $newvalue) {
        $worksheet.Cells($row,$col).Formula = $newvalue
      }
    } # while
  } # if
} # foreach

Step 6 is, after all of the worksheets have been processed, save and close the workbook file.

if (!$workbook.Saved) { $workbook.Save() }
$workbook.Close($False)

Step 7 is to close Excel and clean up the spewage it may leave behind. But also to make sure to release those roach-infested COM objects in reverse order (from how they were instantiated)

$excel.Quit()
while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet) -gt 0) {}

while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheets) -gt 0) {}
while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) -gt 0) {}
[void][System.GC]::Collect()
# and if Excel just won't die...
Get-Process 'excel' | Stop-Process -Force

Step 8 is to grab something to eat. Because coding and talking make me hungry.

Step 9 is to assemble all of this into one script – which, if you’re as lazy as I am, can get here. It also contains some additional exception handling sauce and comment seasoning.

I hope you enjoyed this mindless rambling. The code provided at the link above has been tested and works, for me at least, so if it doesn’t work for you, I’ll respond with “that’ll be addressed in the next build.”. Seriously, if you see a problem with the mess above, please share a comment below. Thank you!

Enjoy!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s