CODEWITHSUNDEEP
excelpowershellforeach
Jgaard
Jgaard

Reputation: 3

Powershell loop for excel replace

I have found a bit of code here on stack overflow, and added a few bits. It works like a charm with the current settings... On Sheet1 in the columns from A to D.

I would like it to run on all the sheets in the workbook. But I cant fold my brain around the a solotion that works.

This is my code that works on Sheet1: 

$replacecount = 0
$file = "H:\test4.xlsx"
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Workbook = $Excel.workbooks.open($file)
$SearchString = "2019-AUG"
$replacestring = "2020-MAY"

$Worksheet = $Workbook.Worksheets.Item(1)
$Range = $Worksheet.Range("A1:D1").EntireColumn

    write-output $worksheet.index

    $Search = $Range.find($SearchString)
    if ($search -ne $null) {
        $FirstAddress = $Search.Address
        do {
            $search.Value() = $Search.value() -Replace $SearchString, $replacestring  
            $search = $Range.FindNext($search)
            $replacecount++
        } while ($search -ne $null -and $search.Address -ne $FirstAddress)
    }

Write-output "status Close"
Write-output $replacecount

$WorkBook.Save()
$WorkBook.Close()

I'm thinking that I should be using a FOREACH (sheet in worksheet) but when I try to set the range inside the loop I get an error

Upvotes: 0

Views: 184

Answers (1)

Parfait
Parfait

Reputation: 107577

Consider looping through Excel's Sheets.Count property:

$num = $Workbook.Sheets.Count()

For ($i = 1; $i -le $num; $i++) {
    write-host $Workbook.Worksheets.Item($i).Name

    $Worksheet = $Workbook.Worksheets.Item($i)
    $Range = $Worksheet.Range("A1:D1").EntireColumn

    write-output $worksheet.index

    $Search = $Range.find($SearchString)
    if ($search -ne $null) {
        $FirstAddress = $Search.Address
        do {
            $search.Value() = $Search.value() -Replace $SearchString, $replacestring  
            $search = $Range.FindNext($search)
            $replacecount++
        } while ($search -ne $null -and $search.Address -ne $FirstAddress)
    }

}

Alternatively, directly loop on worksheets and not via sheet index with Item as @Theo comments:

foreach ($Worksheet in $Workbook.Worksheets){
        write-host $Worksheet.Name

        # $Worksheet = $Workbook.Worksheets.Item($i)   REMOVE THIS ASSIGNMENT
        ...
}

Upvotes: 0

Related Questions