I’m trying to search an Excel column for specific text, I pass the workbook name, sheet number and column letter and the text I’m searching for but it is not found. I know the text I’m searching for is in the column, why is it not being found, what am i doing wrong?
$SearchText = 'I/O operation has been aborted'
$File = 'C:\Iain\Empyrean\DBAReport\DBAReport_20200106_070005.xlsx'
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($File)
$workSheet = $Workbook.Sheets.Item(5)
$Range = $Worksheet.Range("F1").EntireColumn
$Range.Find($SearchText)
Example of entry that I'm searching for:
Time |
ComputerName |
LevelDisplayName |
EventID |
ProviderName |
Message |
1/4/2020 18:16 |
P01 |
Error |
18210 |
MSSQL$PROD03 |
BackupIoRequest::ReportIoError: write failure on backup device '//P03/SQLBackups/PROD03/csprod/P03_PROD03_csprod_Full_202001041721.safe'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.). |
It looks like your spaces are actually the middle dot (interpunct) character (either U+2E31 or U+00B7). Are you using Notepad++ with the show invisible characters option turned on?
When I copied your code, searching for the string
I/O operation has been aborted
does not work, however if I retype the string
I/O operation has been aborted
locally, it does work. I think the Find() method for Excel is searching for the literal interpunct character and not finding it.
Edit Also, just FYI, you have your $workSheet variable as workSheet on line 6 but Worksheet on line 7. This isn’t affecting your functionality, it’s just inconsistent.
The dots are added when I copy & paste from VS Code. But I re-wrote the entire script in Powershell_ise and still get no results.
$SearchText = 'I/O operation has been aborted'
$File = 'C:\Iain\Empyrean\DBAReport\DBAReport_20200106_070005.xlsx'
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($File)
$workSheet = $Workbook.Sheets.Item(5)
$Range = $Worksheet.Range("F1").EntireColumn
$Range.Find($SearchText)
The problem seems to be that my script is only reading row 1 of F column. I want to read all the rows searching for the text…?
I’ve got it working, by adding this
$Rows = ($worksheet.UsedRange.Rows).count
for ($i=1; $i -le $Rows -1; $i++) {
$row = "f$i"
$Range = $Worksheet.Range($row).EntireColumn
$Range.Find($SearchText)
}
After doing some further reading, it seems that Find returns the first match that it discovers in the specified range and then stops, so using EntireColumn just tells Find to check the whole column for a match, not to find every match in the column. However, you can re-use the parameters of a Find search with the FindNext method (which is basically analogous to the way the Find dialogue works in the Excel application).
Here is an example script in which the author implements FindNext in a Do loop after an initial Find search in order to find all matches in the desired search range.