Hello there,
I am writing a script that would search for a string in a workbook, then will pull the required information and use them for further processing.
I have used a function which I found on this website to aid myself:
Find Data in Excel Using PowerShell
#Select the Mac address file and VM Inventory file
#
#Mac Address File
$MacFileBrowser = ''
$MacFileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{
InitialDirectory = [Environment]::GetFolderPath('Desktop')
Filter = 'Textfiles (*.txt)|*.txt'
}
$null = $MacFileBrowser.ShowDialog()
#VM inventory file
$VMSourceFileBrowser = ''
$VMSourceFileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{
InitialDirectory = [Environment]::GetFolderPath('Desktop')
Filter = 'SpreadSheet (*.xls)|*.xls'
}
$null = $VMSourceFileBrowser.ShowDialog()
Function Search-Excel {
[cmdletbinding()]
Param (
[parameter(Mandatory)]
[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
}
$MACAddresses = Get-Content -Path ($MacFileBrowser).FileName
ForEach ($MACAddresse in $MACAddresses) {
Search-Excel -Source $VMSourceFileBrowser.FileName -SearchText $MACAddresse |
Format-Table
}
I get the following when I run the scrip:
AVERTISSEMENT : [SRV1-2022-09-06-04-40] Nothing Found!
AVERTISSEMENT : [SRV-01-2022-09-06-04-42] Nothing Found!
AVERTISSEMENT : [SRV-02-2022-09-06-05-09] Nothing Found!
AVERTISSEMENT : [SRV-03-2022-09-07-10-00] Nothing Found!
AVERTISSEMENT : [SRV-tt-2022-09-07-10-00] Nothing Found!
AVERTISSEMENT : [SRV-06-2022-09-07-10-02] Nothing Found!
AVERTISSEMENT : [SRV-022022-09-07-10-03] Nothing Found!
AVERTISSEMENT : [SRV-10=2022-09-07-10-05] Nothing Found!
AVERTISSEMENT : [SRV-11-022022-09-07-10-11] Nothing Found!
AVERTISSEMENT : [SRV-12-2022-09-07-10-12] Nothing Found!
AVERTISSEMENT : [SRV-Z-2022-09-07-10] Nothing Found!
AVERTISSEMENT : [SRV-Y-07-2022-09-08-02-12] Nothing Found!
AVERTISSEMENT : [SRV-U–08-2022-09-08-02-46] Nothing Found!
AVERTISSEMENT : [SRV-I-09-2022-09-08-02-47] Nothing Found!
AVERTISSEMENT : [SRV-K-008-02-48] Nothing Found!
AVERTISSEMENT : [SRV-N–10-2022-09-08-02-48] Nothing Found!
AVERTISSEMENT : [SRV-TEST-02 2022-09-08-02] Nothing Found!
AVERTISSEMENT : [Feuil1] Nothing Found!
WorkSheet Column Row Text Address
Boubul-2022-09-06-04-42 6 2 00155D6A2A03 ‘[Inventaire.xls]Boubul-2022-09-06-04-42’!F2
My questions are:
1. I would like to use the name of the worksheet to to set it as the Server Name?
2-. How can I add another column into the output which finds the next cell next to the string found the workbook? Say, if the string is found on worksheet1, cell B3, then I need to have the value of B4.
If you have any otherway which seem to be easier to search in a workbook with powershell, please do not hesitate to suggest it.
Thank you in advance.
Kind regards,
Boulbul
… since you are controlling the source, the intermediate data store and the technology you are free to use whatever helps you accomplishing your task, right?
Usually you have an object - for example 
