Hello,
I am having some problems to try to implement this script. I have one big excel with several columns and I would like to search different values returning not only the row and column of that value but also other values of columns in that row. Usually the important column where the value is found is number 28 and I would like to have the data also in the same row of columns number 9 and 33, even to write all that output in a txt or dat.
<#
Script to find in excel file .
#>
$ErrorActionPreference = 'Stop'
Function Search-Excel
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)][String]$ExcelPath,
[Parameter(Mandatory=$true)][String]$SearchText
)
Try {
# Open the target excel
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($ExcelPath)
# iterate all sheets
$i = 0;
While ($i -lt $workbook.Sheets.Count) {
$curSheet = $workbook.Sheets.Item($i + 1)
$i += 1
$searchResults = Search-Sheet -Sheet $curSheet -SearchText $SearchText
Write-Host "Sheet: $($curSheet.Name)"
$searchResults | ForEach-Object {
Write-Host "Row: $($_.Row), Column: $($_.Column)"
}
Write-Host ""
}
# Release Excel Com Object resource
$workbook.Save()
$excel.Visible = $true
Start-Sleep 5
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
} Catch {
Throw $_
}
}
Function Search-Sheet
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)]$Sheet,
[Parameter(Mandatory=$true)][String]$SearchText
)
$firstResult = $result = $Sheet.UsedRange.Find($SearchText)
$allResults = New-Object System.Collections.ArrayList
If ($firstResult -eq $null) {
Return $allResults
}
$processedResult = Compose-SearchResult -Result $result
$allResults.Add($processedResult) | Out-Null
$isSearchEnd = $false
Do {
$result = $Sheet.UsedRange.FindNext($result)
$isSearchEnd = Test-SearchResultSame -Result1 $result -Result2 $firstResult
If (-not $isSearchEnd) {
$processedResult = Compose-SearchResult -Result $result
$allResults.Add($processedResult) | Out-Null
}
} While (-not $isSearchEnd)
Return $allResults
}
Function Compose-SearchResult
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)]$Result
)
$composedResult = New-Object -Type psobject
$composedResult | Add-Member -MemberType NoteProperty -Name Column -Value $Result.Column
$composedResult | Add-Member -MemberType NoteProperty -Name Row -Value $Result.Row
Return $composedResult
}
Function Test-SearchResultSame
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)]$Result1,
[Parameter(Mandatory=$true)]$Result2
)
Return ($Result1.Column -eq $Result2.Column) -and ($Result1.Row -eq $Result2.Row)
}
# 0. Prepare excel name and search text
$ExcelFilePath = "C:\Users\V3.xlsx"
$SearchText = "Windows"
Search-Excel -ExcelPath $ExcelFilePath -SearchText $SearchText
The outcome here is
Sheet: Book1 Row: 7745, Column: 27 Row: 7745, Column: 28 Row: 24671, Column: 27 Row: 24671, Column: 28 Row: 25319, Column: 27
And I would like to have also
Version : , Server: , Row: 7745, Column: 27,
Thank you