CODEWITHSUNDEEP
excelstringpowershellfull-text-search
Don_swcc
Don_swcc

Reputation: 31

How to search an entire excel workbook for a particular string using powershell

I need to search for a particular string in a excel spreadsheet that has multiple sheets in it. I am looking for a way to search the entire contents of the excel file similar to the Find All option in Excel with the scope set to the workbook and not just the worksheet.

It would be really nice if there was something similar to the search string for a particular string in regular file, (ie)

gci xcelfile.xls | select-string -pattern $mySearchString

I have searched the internet and I don't see much existing information for searching the contents of an existing excel file using powershell. I am hoping I can get some pointers here to get me to my goal.

Any assistance is much appreciated. Thanks Don

Upvotes: 2

Views: 8459

Answers (1)

ArcSet
ArcSet

Reputation: 6860

Opens Excel

Loads File

Loops through each worksheet

Searches a range

Loops through find next Outputs Index $Column$Row

Exits Excel

$File = "C:\TEST.xlsx"
$SearchString = "TEST"
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($File)
for($i = 1; $i -lt $($Workbook.Sheets.Count() + 1); $i++){
    $Range = $Workbook.Sheets.Item($i).Range("A:Z")
    $Target = $Range.Find($SearchString)
    $First = $Target
    Do
    {
        Write-Host "$i $($Target.AddressLocal())"
        $Target = $Range.FindNext($Target)
    }
    While ($Target -ne $NULL -and $Target.AddressLocal() -ne $First.AddressLocal())
}
$Excel.Quit()

Upvotes: 2

Related Questions