2

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?

Silentbob
  • 2,805
  • 7
  • 38
  • 70

1 Answers1

4

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.

  • From your code above I am not sure how it would tell me the cell reference? – Silentbob Sep 30 '15 at 13:21
  • Took me a while to find the correct numerical parameters for the [Range.Address property](https://msdn.microsoft.com/en-us/library/office/ff837625.aspx). I'm used to using it in VBA. –  Sep 30 '15 at 13:41
  • Is there a way to improve it so it finds each time the word German appears? – Silentbob Oct 01 '15 at 12:26