Created
October 1, 2019 15:49
-
-
Save smartexpert/b90fc859378c9e7c729d6cec30793b0e to your computer and use it in GitHub Desktop.
PowerShell Excel String Search
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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