Skip to content

Instantly share code, notes, and snippets.

@smartexpert
Created October 1, 2019 15:49
Show Gist options
  • Save smartexpert/b90fc859378c9e7c729d6cec30793b0e to your computer and use it in GitHub Desktop.
Save smartexpert/b90fc859378c9e7c729d6cec30793b0e to your computer and use it in GitHub Desktop.
PowerShell Excel String Search
# Credits to Boe Prox for his article on https://mcpmag.com/articles/2018/04/06/find-excel-data-with-powershell.aspx
# from which this code is pulled
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)) {
# Find Method https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel
$Found = $WorkSheet.Cells.Find($SearchText) #What
If ($Found) {
# Address Method https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-address-property-excel
$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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment