excelpowershellexcel.application

Powershell add 1 to excel column reference


Just asked a powershell question here Finding excel cell reference and I need to add to it.

The overall code I ended up with is as follows.

$filePath = "c:\temp\test.xlsx"

if (test-path $filePath) {
$wb = $xl.Workbooks.Open($filePath)
$ws = $wb.Worksheets.Item("sheet1")
if ([bool]$ws.cells.find("German           Baseload")) {write-host $ws.cells.find("German           Baseload").address(0, 0, 1, 0)}
}

This returns a cell reference of F25 which is where the string is located, based on this I want to test the cell next to it in cell reference G25, my question is how do I add one column to F25?


Solution

  • Accessing any cell from a known cell reference is simply a matter of applying Range.Offset property to the original cell reference.

    $filePath = "T:\TMP\findit.xlsx"
    $xl = New-Object -ComObject Excel.Application
    $xl.Visible = $true
    if (test-path $filePath) {
    $wb = $xl.Workbooks.Open($filePath)
    $ws = $xl.WorkSheets.item("sheet1")
    if ([bool]$ws.cells.find("German")) 
        {
        $found = 1
        $rc1 = $ws.cells.find("German")
        $rc2 = $rc1.offset(0, 1)
        write-host $found
        write-host $rc1.address(0, 0, 1, 1)
        write-host $rc2.address(0, 0, 1, 1)
        write-host $ws.cells.find("German").offset(0, 1).address(0, 0, 1, 1)
        }
    }
    

    I've reported the offset cell address back redundantly as a way of confirmation.