How to iterate over an Excel Range in PowerShell?-CodePudding

This might be easy, but I am really struggling to write code that accomplishes this!

Just as a concept, I want to be able to write down a range of Excel cells in Powershell, and then iterate over each cell with a loop, and sum the value in each cell.

$range="A1:B2".Split(":")

$startrow= $range[0] -replace "[^0-9]" #equals 1
$startcol= $range[0] -replace "[^A-Z]" #equals "A"
$maxrow= $range[1] -replace "[^0-9]" #equals 2
$maxcol= $range[1] -replace "[^A-Z]" #equals "B"
$row = $startrow; $col = $startcol; $value = 0
while($col -le $maxcol){
$row = $startrow
    while($row -le $maxrow){
    $cellValue = [int]$ws_test.Cells.Item($row,$col).Text
    $value = $value   $cellValue
    $row  
    }
$col  
}

This is as far as I got. The nested while loop would work if I could convert the macros $startcol and $maxcol to integers, but I can't figure out how to do that (ex: convert "A" to 1, or "AA" to 27).

Is there another method I could look into to accomplish this? I'm pretty new to PowerShell and have been struggling with this for the last few days.

CodePudding user response：

You can define the range of cells on the worksheet, then iterate over each cell in the range:

    Foreach ($cell in $worksheet.Range("A1:B10")) {
        # Process cell values here
    }

https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.range