0

I need to do a case insensitive search in an Excel document using Range.Find.

I'm currently using the following command as an attempt do a case insensitive search for any email address returned by https://haveibeenpwned.com

    $Found = $WorkSheet.Cells.Find($SearchText, $null, "xlValues", "xlWhole", "xlByRows", 1, $false) #What, After, Lookin, LookAt, SearchOrder, MatchCase

It returns:

WARNING: [] No public exploits found!
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
At C:\Users\qqqq\Documents\incidents\Search-PwnAddress.ps1:31 char:9
+         $Found = $WorkSheet.Cells.Find($SearchText, $null, "xlValues" ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

How do I properly do a Range Find so that I can do a case insensitive search?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Ran Dom
  • 315
  • 5
  • 13
  • 1
    You wouldn't pass "xlValues", "xlWhole", "xlByRows" as a string - each of these has a corresponding value - e.g https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xllookat-enumeration-excel – BigBen Aug 02 '18 at 19:58
  • And I think you'd use `[Type]::Missing` instead of `$null` - https://stackoverflow.com/questions/17483483/powershell-call-method-with-optional-arguments – BigBen Aug 02 '18 at 20:14
  • Thank you, both answers combined were able to point me in the right direction, many ways to slice this problem aparrently – Ran Dom Aug 02 '18 at 20:17

1 Answers1

0

From Range.Find - note that xlValues, xlWhole, and xlByRows are not referred to as Strings, but constants. They are members of specific Enumerations - the XlFindLookIn, XlLookAt and XlSearchOrder enumerations.

Enumerations have members with descriptive names that reference a specific value - so in this case, you can use the corresponding value as your argument. Trying to pass their names as Strings - i.e. "xlValues" - will throw a "Type Mismatch" error.

  • xlValues: -4163
  • xlWhole: 1
  • xlByRows: 1

Note also that you should use [Type]::Missing instead of $null, as this answer suggests.

BigBen
  • 46,229
  • 7
  • 24
  • 40