In Powershell, how do I match whole cell for Excel Find/FindNext? RRS feed

  • Question

  • I want to do Find and FindNext functions which match the entire contents of the cell.  I have these functions working but the default is to match any part of the cell contents which results in my getting too many matches.  I am trying to count how many times specific text appears in a column of data.

    Below is just one of many attempts.  The "$find" line is the one that fails.  It fails with different errors depending on the parameters passed.  For example, if I pass it as shown below I get:

    Error: exception calling 'find' with '4' arguments: Type mismatch exception 0x80020005

    If I pass the parameters as: $amt,,,1

    I get the syntax error: missing expression after ','

    This link is the Microsoft description of this function:

    $xlsx  = "C:\users\me\TEST.xlsx"
    write-host "Testing FIND for $xlsx" -fore cyan
    $excel = new-object -comobject Excel.Application
    $excel.Visible = $True
    $xlb = $excel.Workbooks.Open($xlsx)
    $xls = $xlb.Sheets.Item(1)
    $range = $xls.range("C1").EntireColumn
    $amt   = 9.9    
    $whole = 1      
    $find  = $range.find($amt,$null,$null,$whole)
    if ($find)
      { write-host "FIND ok: $amt" -fore green
        $find2 = $range.findnext($find)
        if ($find.row -ne $find2.row)
          { write-host "FINDNEXT ok: $amt" -fore green }
          { write-host "FINDNEXT - not found: $amt" -fore yellow }
      { write-host "FIND - not found: $amt" -fore yellow }

    Any ideas?  I'd prefer to keep it as Find/Findnext but am open to alternative ways to count the number of times a string occurs in a range.

    Tuesday, April 29, 2014 4:19 PM


All replies