Powershell script to search through a directory of excel files to find a string only searching through 1 file

Question

I have found this script on https://shuaiber.medium.com/ I want to use it to find a certain string in a folder full of excel files. the problem I am encountering is that it basically only searches through 1 file and then stops...

here is the script

Function Search-Excel {
    [cmdletbinding()]
    Param (
        [parameter(Mandatory, ValueFromPipeline)]
        [ValidateScript({
            Try {
                If (Test-Path -Path $_) {$True}
                Else {Throw "$($_) is not a valid path!"}
            }
            Catch {
                Throw $_
            }
        })]
        [string]$Source,
        [parameter(Mandatory)]
        [string]$SearchText
        #You can specify wildcard characters (*, ?)
    )
    $Excel = New-Object -ComObject Excel.Application
    Try {
        $Source = Convert-Path $Source
    }
    Catch {
        Write-Warning "Unable locate full path of $($Source)"
        BREAK
    }
    $Workbook = $Excel.Workbooks.Open($Source)
    ForEach ($Worksheet in @($Workbook.Sheets)) {
       
        $Found = $WorkSheet.Cells.Find($SearchText) #What
        If ($Found) {
            
            $BeginAddress = $Found.Address(0,0,1,1)
            #Initial Found Cell
            [pscustomobject]@{
                WorkSheet = $Worksheet.Name
                Column = $Found.Column
                Row =$Found.Row
                Text = $Found.Text
                Address = $BeginAddress
            }
            Do {
                $Found = $WorkSheet.Cells.FindNext($Found)
                $Address = $Found.Address(0,0,1,1)
                If ($Address -eq $BeginAddress) {
                    BREAK
                }
                [pscustomobject]@{
                    WorkSheet = $Worksheet.Name
                    Column = $Found.Column
                    Row =$Found.Row
                    Text = $Found.Text
                    Address = $Address
                }                 
            } Until ($False)
        }
        Else {
            Write-Warning "[$($WorkSheet.Name)] Nothing Found!"
        }
    }
    $workbook.close($false)
    [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel)
    [gc]::Collect()
    [gc]::WaitForPendingFinalizers()
    Remove-Variable excel -ErrorAction SilentlyContinue
}

And then I would use

Get-ChildItem -Path "C:\excelfiles" -Recurse -Include *.xls, *.xlsx, *.xlsm | Select-Object -Property Directory, Name | ForEach-Object { "{0}{1}" -f $.Directory, $.Name } | Search-Excel -SearchText MyText

I know its only searching through 1 file because I looked at another file and tried to get it to send me back to confirm yet it doesnt work.

Any help would be greatly appreciated.

Answer 1

So let's talk about a couple of things with your command.

Get-ChildItem -Path "C:\excelfiles" -Recurse -Include *.xls, *.xlsx, *.xlsm | Select-Object -Property Directory, Name | ForEach-Object { "{0}{1}" -f $.Directory, $.Name } | Search-Excel -SearchText MyText

If we break this up into the individual pieces (separated by pipes), we might be able to figure out what's wrong. The first part, Get-ChildItem, is only looking for files matching *.xls, *.xlsx, and *.xlsm, so already directories are excluded. Well, what exactly does this function return? If you were to look at the object types, you'll see System.IO.FileInfo, which has a bunch of properties built in. One of which is the full file path needed, .FullName.

Currently, Search-Excel is setup to only search one file. If you want to search multiple files at once, you'll need a loop somewhere. In my opinion, the easiest place to do that will be outside of the function, like this:

Get-ChildItem -Path "C:\excelfiles" -Recurse -Include *.xls, *.xlsx, *.xlsm | Foreach-Object { Search-Excel -Source $_.FullName -SearchText MyText }

Answer 2

You're going to need to include a loop within your function, or put the function within a ForEach-Object loop. For the function change you could do:

Function Search-Excel {
    [cmdletbinding()]
    Param (
        [parameter(Mandatory, ValueFromPipeline)]
        [ValidateScript({
            Try {
                If (Test-Path -Path $_) {$True}
                Else {Throw "$($_) is not a valid path!"}
            }
            Catch {
                Throw $_
            }
        })]
        [string]$Source,
        [parameter(Mandatory)]
        [string]$SearchText
        #You can specify wildcard characters (*, ?)
    )
    $Excel = New-Object -ComObject Excel.Application
    ForEach($Path in $Source){
###     <the rest of your existing code here, adjusted to work with $Path instead of $Source>
    }
    [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel)
    [gc]::Collect()
    [gc]::WaitForPendingFinalizers()
    Remove-Variable excel -ErrorAction SilentlyContinue
}

Or, to work with your existing function you could just do:

Get-ChildItem -Path "C:\excelfiles" -Recurse -Include *.xls, *.xlsx, *.xlsm |
    ForEach-Object { Search-Excel -Source $_.FullName -SearchText MyText }