Powershell find excel cell reference

Question

I am using the following powershell code to search through a excel document for a string and return true or false depending on if its found.

if (test-path $filePath) {
$wb = $xl.Workbooks.Open($filePath)
if ([bool]$xl.cells.find("German")) {$found = 1}
}

I want to be able to get the cell reference of the string if its found but I cant figure it out or find an answer on google. Can you help?

Answer 1

While there is a method to search through an entire workbook for a value, typically a Range.Find method is performed on a worksheet. You are setting a var to the workbook but still using the application as the search. You should be getting the worksheet to search from the workbook and using that as the target of the Find operation.

Following are some suggested modifications to your PS1.

$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
    write-host $found
    write-host $ws.cells.find("German").address(0, 0, 1, 1)
    }
}

To continue the search for all occurrences use the Range.FindNext method until you loop back to the original cell address.

$filePath = "T:\TMP\findit.xlsx"
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
if (test-path $filePath) {
$wb = $xl.Workbooks.Open($filePath)
$ws = $wb.WorkSheets.item("sheet1")

$rc1 = $ws.cells.find("German")
if ($rc1) 
    {
    $found = 1
    $addr = $rc1.address(0, 0, 1, 0)
    do
        {
        $rc1 = $ws.cells.findnext($rc1)
        write-host $rc1.address(0, 0, 1, 0)
        } until ($addr -eq $rc1.address(0, 0, 1, 0))
    }
}

It's hard to provide much more than generalities since so much of your code is missing. I've filled in the missing information with my own test environment.