CODEWITHSUNDEEP
excelpowershellexcel.application
Silentbob
Silentbob

Reputation: 3065

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?

Upvotes: 1

Views: 1144

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Related Questions