Change a String of Text in Excel on Multiple Files with Powershell-CodePudding

I have multiple Excel files that have similar data. I wanted to have a PowerShell script run through all the excel files in a specific folder and replace a keyword or multiple keywords in the table and change it to something else. I am new to shell this is what I have so far. The script runs opens all my excel files but does not change anything. Keep in mind all the data I need changed is on sheet 2 (LWS)

$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()    `

CodePudding user response：

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()