Replace a string of text in multiple Excel files using a PowerShell script

Question

I have multiple Excel files that have similar data, and I want a PowerShell script that searches and replaces a string of text in all Excel files in a specified folder.

So far, I have the following PowerShell script that opens all my Excel files and attempts to replace matching strings in the second Worksheet, but the expected changes are not made:

$Path = "C:\Users\mabrant\Downloads\Workstations (21)\Workstations\"
$files = Get-ChildItem "C:\Users\mabrant\Downloads\Workstations (21)\Workstations" -Filter *.xlsx

ForEach ($item in $files) { 
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($Path + [System.IO.Path]::GetFileName("$item"))
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(2)
$SearchString = "NEW" #String to Find
$Range = $Worksheet.Range("S4:Y4").EntireColumn #Range of Cells to look at
$Search = $Range.find($SearchString) }

$Search = $Range.find($SearchString)
if ($search -ne $null) {
    $FirstAddress = $search.Address
    do {
        $Search.value() = "Installed" # Replacement Value
        $search = $Range.FindNext($search)
    } while ( $search -ne $null -and $search.Address -ne $FirstAddress)
}

$WorkBook.Save()
$WorkBook.Close()
[void]$excel.quit()    `

do you have to use excel .com objects? can you just edit it as a .csv? — another victim of the mouse
– another victim of the mouse, Commented Jun 23, 2022 at 21:33

Chris Matuszewski · Accepted Answer · 2022-06-24 08:42:33Z

1

Seems like you were really close to having a working script. I believe the main problem is that your ForEach block should include everything except the $excel.quit() so that you save and close each workbook as you go.

I reformatted your code to make it easier to see the entire ForEach block, I removed the duplicate $Search = $Range.find($SearchString) statement, and I set some Excel.Application properties to $false to make it work better.

Here is the updated code:

$Path = "C:\Users\mabrant\Downloads\Workstations (21)\Workstations\"
$files = Get-ChildItem $Path -Filter *.xlsx

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.EnableEvents = $false
$Excel.DisplayAlerts = $false

ForEach ($item in $files) {
  $Workbook = $Excel.Workbooks.Open($Path + [System.IO.Path]::GetFileName("$item"))
  $Worksheet = $Workbook.Worksheets.Item(2)
  $SearchString = "NEW" #String to Find
  $Range = $Worksheet.Range("S4:Y4").EntireColumn #Range of Cells to look at

  $Search = $Range.find($SearchString)
  if ($Search -ne $null) {
      $FirstAddress = $Search.Address
      do {
          $Search.Value() = "Installed" # Replacement Value
          $Search = $Range.FindNext($Search)
      } while ( $Search -ne $null -and $Search.Address -ne $FirstAddress)
  }

  $WorkBook.Save()
  $WorkBook.Close()
}
$Excel.Quit()

answered Jun 24, 2022 at 8:42

Chris Matuszewski

504 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

EEVolutionX Over a year ago

Question for you if i wanted to search blank cells in a column and fill it with N/A, but only within a table. I change my searchstring to "" for all blanks but it goes continuously and does not stop till I stop the code lol.

Chris Matuszewski Over a year ago

@EEVolutionX I believe it's because you used EntireColumn in your search range. If you know you only need to work with S4:Y4, you can just drop .EntireColumn. If you want to search a specific column of a table range you could use something like $Range = $Worksheet.ListObjects("Table1").ListColumns("Column3").Range. You could also use a lookup function if you want to select a certain row within the named table.

Chris Matuszewski Over a year ago

@EEVolutionX can you accept my answer if it answered your original question, or can you clarify your question if I didn't answer it? Thanks.

EEVolutionX Over a year ago

Thanks for all your help!!!. Sorry didnt know how to do that i just accepted it.

Chris Matuszewski Over a year ago

@EEVolutionX do you mind upvoting my answer if you found it useful? Thanks!

Collectives™ on Stack Overflow

Replace a string of text in multiple Excel files using a PowerShell script

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related