May I ask why you’re not using the specialized module ImportExcel? If I remember right you already used it, don’t you? 
Yes I did. I thought that the function above works with the same module?..I guess I did not read well. 
Thank you.
Hi Olaf,
I could not find anyway to do it with import-excel module…could not find much about finding something in the entire workbook…
I would like to stick to the code above, cause it does most of what I need already. My only problem at this stage is that I stored the output into a variable, and I cannot extract data from it.
This is a last part of the code above:
$MACAddresses = Get-Content -Path ($MacFileBrowser).FileName
ForEach ($MACAddresse in $MACAddresses) {
#$Hote = $Worksheet.Name -replace ".{17}$"
#Search-Excel -Source ($VMSourceFileBrowser).FileName -SearchText $MACAddresse |
Format-Table
$OutPut = Search-Excel -Source ($VMSourceFileBrowser).FileName -SearchText $MACAddresse |
Format-Table
}
The $Output variable has now the following value:
WorkSheet Column Row Text Address
--------- ------ --- ---- -------
Boubul-2022-09-06-04-42 6 2 00155D6A2A03 '[Inventaire.xls]Boubul-2022-09-06-04-42'!F2
When I ran $OutPut |select Worksheet, it does not display the worksheet name.
Worksheet
---------
Any help please.
Thank you in advance.
Kind regards,
Boulbul
What does the following command show?
$output | Get-Member
Thanks for getting back to me.
The output of
is
TypeName : Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
autosizeInfo Property Microsoft.PowerShell.Commands.Internal.Format.AutosizeInfo, System.Management.Automati...
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property string ClassId2e4f51ef21dd47e99d3c952918aff9cd {get;}
groupingEntry Property Microsoft.PowerShell.Commands.Internal.Format.GroupingEntry, System.Management.Automat...
pageFooterEntry Property Microsoft.PowerShell.Commands.Internal.Format.PageFooterEntry, System.Management.Autom...
pageHeaderEntry Property Microsoft.PowerShell.Commands.Internal.Format.PageHeaderEntry, System.Management.Autom...
shapeInfo Property Microsoft.PowerShell.Commands.Internal.Format.ShapeInfo, System.Management.Automation,...
TypeName : Microsoft.PowerShell.Commands.Internal.Format.GroupStartData
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property string ClassId2e4f51ef21dd47e99d3c952918aff9cd {get;}
groupingEntry Property Microsoft.PowerShell.Commands.Internal.Format.GroupingEntry, System.Management.Automat...
shapeInfo Property Microsoft.PowerShell.Commands.Internal.Format.ShapeInfo, System.Management.Automation,...
TypeName : Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property string ClassId2e4f51ef21dd47e99d3c952918aff9cd {get;}
formatEntryInfo Property Microsoft.PowerShell.Commands.Internal.Format.FormatEntryInfo, System.Management.Autom...
outOfBand Property bool outOfBand {get;set;}
writeStream Property Microsoft.PowerShell.Commands.Internal.Format.WriteStreamType, System.Management.Autom...
TypeName : Microsoft.PowerShell.Commands.Internal.Format.GroupEndData
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property string ClassId2e4f51ef21dd47e99d3c952918aff9cd {get;}
groupingEntry Property Microsoft.PowerShell.Commands.Internal.Format.GroupingEntry, System.Management.Automat...
TypeName : Microsoft.PowerShell.Commands.Internal.Format.FormatEndData
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property string ClassId2e4f51ef21dd47e99d3c952918aff9cd {get;}
groupingEntry Property Microsoft.PowerShell.Commands.Internal.Format.GroupingEntry, System.Management.Automat...
Kind regards,
Boulbul
This is unfartunately not helpful at all … and here is why …
Look at the output of this:
Get-Process | Select-Object -First 1
Now we want to know what methods and properties the outputted object has. We do this:
Get-Process | Select-Object -First 1 | Get-Member
But when you use a format cmdlet like Format-Table:
Get-Process | Select-Object -First 1 | Format-Table | Get-Member
The output is useless.
You may make a big step back and start with explaining what you actually want to do - the big picture - not the way you think you have to go to get to your expected result.
You wrote …
Do you really have to search the whole workbook? Or can you narrow the area you want to search? Maybe to a specific column …
Yeah I totally missed the format command. If that is removed the initial select command should work as expected
Hi Olaf,
Thanks for getting back to me. …
I have an inventory file with all VMs avalable on the network. I would like to be able to use the MAC address listed on the txt file and search in on the inventory file (xl) file.
The XL file has multiple Sheets, each sheet represents a different cluster (Please refere to the following links)
How to Retrieve VMs details from multiple Clusters
Export the output of a script into a new worksheet in an exsiting file in XL
If an instance of the Mac address is found, then the worksheet name, the Host, IP and MAC of VM will be displayed on screen.
After that, I would launch a command to restart the VM using these information.
So, instead of doing this manually, I would like to be able to restart the VMs all at once using the script. This will be on the condition that there should not be duplicates of MAC addresses on the search results. If duplicates are found then the script will ignore these MAC addresses…
Hopefully that is clearer…Allthough, I would like to stick to the this way, cause it works so far. I don’t mind to know what other approaches you might have to tackle such a script.
Yes, it works now!!!..Thank you Krzydoug/Olaf…wonderfulll…so, glad.
Now the script output looks like this:
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!
WorkSheet : Boubul-2022-09-06-04-42
Column : 6
Row : 2
VMMacAddress : 00155D6A2A03
VMName : Boubul-2022-09-06-04-42
VMIPAddress :
Hote : Boubul
Address : '[inventorytest2.xlsx]Boubul-2022-09-06-04-42'!F2
Cliquez sur Entrée pour continuer... :
Now, my next question is… I have a reference to the Mac Address [inventorytest2.xlsx]Boubul-2022-09-06-04-42'!F2. Knowing that the IP address is one cell on the right of the mac and host is 3 cells to the left of the Mac on the XL sheet. How can I find them from this code below please?
Thanks for in advance,
Kind regards,
Boulbul
For me there is an easy solution for this problem … 
… 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?
The conclusion for me would be to NOT use Excel as my data store since it does not provide any advantage over CSV files. You can use even a folder structure or some structure in the file names of the CSV files to store some information about a cluster or cluster nodes contained in the files.
It should be even possible to store all needed information in one CSV file if you add some columns for the information you store at the moment in the Excel sheet names.
Since you are the one who saves those information in these files you know what the names of the columns are. You just have to use the information you already have. 
HI again Olaf,
I am not so sure if I understood your message…Cannot see the difference between the excel and csv…but am interested to know…would you have a link to a similar request pls?
[quote="Olaf, post:11, topic:20412"]
Since you are the one who saves those information in these files you know what the names of the columns are. You just have to use the information you already have
[/quote]
Yes, I know where the information is located…so, would I use import-excel for this or powershell commands pls?
Kind regards,
Boulbul
Excel is made to be used by humans while CSV files are an easily computer readable data format. The ability to use CSV files is built in PowerShell while you need to install a module to be able to work with Excel files. And I’d expect the code to handle CSV files to be way easier than the code to work with Excel files. If you don’t have to use Excel files for a particular reason I’d recommend not to.
Olaf,
Thanks for the Highlight …this is good to know, I will keep this in mind for next time…I am midway of my journey for this script though 
The lesson that I learnt til now I have to be clear on my objective so to get the best answers.
Kind regards,
Boulbul
Olaf,
Sorry to bother yoiu again on this …
Yes, I know. where the locations are…should I use the PSEXcel (Function Get-CellValue to solve this problem? or with Import-excel module…or just with powershell commands?
Thanks
I don’t know.
Cool. 
Usually you have an object - for example $FoundItem. If the MAC address is located in $FoundItem.MACAddress the IP address should be in $FoundItem.IPAddress and the host in $FoundItem.Host. … something like this
Again - I think you’re overcomplicating this a lot. I would never use Excel as a data store. For me an Excel file is the step in a process of a query I give to a manager. I would even refuse to use an Excel file as input for any further processing.
Olaf,
I
,
I know, but have no choice…I don’t have much left to do…well, I think 
I have run the variable $Found with select -object * on the script above.which gets me the Mac address value.
the output was as follows:
Found : System.__ComObject
BeginAddress : '[inventorytest2.xlsx]Boubul-2022-09-06-04-42'!F2
WorkSheet : Boubul-2022-09-06-04-42
Column : 6
Row : 2
VMMacAddress : @{Application=Microsoft.Office.Interop.Excel.ApplicationClass; Creator=1480803660; Parent=System.__ComObject; AddIndent=False; Areas=System.__ComObject; Borders=System.__ComObject;
Cells=System.__ComObject; Column=6; Columns=System.__ComObject; ColumnWidth=14,86; Count=1; CurrentArray=; CurrentRegion=System.__ComObject; Dependents=; DirectDependents=; DirectPrecedents=;
EntireColumn=System.__ComObject; EntireRow=System.__ComObject; Font=System.__ComObject; Formula=AAAAAAAAAAAA; FormulaArray=AAAAAAAAAAAA; FormulaLabel=; FormulaHidden=False;
FormulaLocal=AAAAAAAAAAAA; FormulaR1C1=AAAAAAAAAAAA; FormulaR1C1Local=AAAAAAAAAAAA; HasArray=False; HasFormula=False; Height=15; Hidden=; HorizontalAlignment=1; IndentLevel=0;
Interior=System.__ComObject; Left=622,5; ListHeaderRows=0; LocationInTable=; Locked=True; MergeArea=System.__ComObject; MergeCells=False; Name=; _NewEnum=System.__ComObject; Next=System.__ComObject;
NumberFormat=General; NumberFormatLocal=Standard; Orientation=-4128; OutlineLevel=; PageBreak=; PivotField=; PivotItem=; PivotTable=; Precedents=; PrefixCharacter=; Previous=System.__ComObject;
QueryTable=; Row=2; RowHeight=15; Rows=System.__ComObject; ShowDetail=; ShrinkToFit=False; SoundNote=System.__ComObject; Style=System.__ComObject; Summary=; Text=AAAAAAAAAAAA; Top=15;
UseStandardHeight=True; UseStandardWidth=False; Validation=System.__ComObject; Value2=AAAAAAAAAAAA; VerticalAlignment=-4107; Width=81,75; Worksheet=System.__ComObject; WrapText=False; Comment=;
Phonetic=System.__ComObject; FormatConditions=System.__ComObject; ReadingOrder=-5002; Hyperlinks=System.__ComObject; Phonetics=; ID=; PivotCell=; Errors=System.__ComObject;
SmartTags=System.__ComObject; AllowEdit=True; ListObject=; XPath=System.__ComObject; ServerActions=; MDX=; CountLarge=1; SparklineGroups=System.__ComObject; DisplayFormat=System.__ComObject;
HasRichDataType=False; CommentThreaded=; LinkedDataTypeState=0; HasSpill=; SpillingToRange=; SpillParent=; Formula2=; Formula2Local=; Formula2R1C1=; Formula2R1C1Local=; SavedAsArray=}
VMName :
VMIPAddress :
Hote : Boubul
Address : '[inventorytest2.xlsx]Boubul-2022-09-06-04-42'!F2
now with the $Found.Cell object I get the value of AAAAAAAAAAAA in the mac address
Found : System.__ComObject
BeginAddress : '[inventorytest2.xlsx]Boubul-2022-09-06-04-42'!F2
WorkSheet : Boubul-2022-09-06-04-42
Column : 6
Row : 2
VMMacAddress : AAAAAAAAAAAA
VMName :
VMIPAddress :
Hote : Boubul
Address : '[inventorytest2.xlsx]Boubul-2022-09-06-04-42'!F2
Form the objects listed above, is there anyway to get the value of the IP and VMname know that they are in e.g: Collumn 5 row 2 ?
Thanks mate.
KInd regards,
Boulbul
Sorry. But this is bullshit. 
Here’s a little example of what I mean:
$Stones = @'
FirstName,LastName,Instrument
Mick,Jagger,voice
Ron,Wood,guitar
Keith,Richards,guitar
Charlie,Watts,drums
'@ |
ConvertFrom-Csv
$SearchPattern = Read-Host -Prompt 'Please enter a first name of one of the Rolling Stones'
$Result = $Stones | Where-Object -Property FirstName -EQ -Value $SearchPattern
'{0} {1} was member of the Rolling Stones. His instrument was {2}.' -f $Result.FirstName, $Result.LastName, $Result.Instrument
Using Excel as your data store adds an additional layer of complexity you’re obviously not prepared for yet. Drop it and use the tools PowerShell offers you out of the box. It will make your solution more reliable and robust and your code way easier to under stand and to maintain.
Olaf,
You are right…it is not about choice but about time rather…
Your example is very simple and easy to understand…I think that I can incorporate it into my script. Once I know which sheet contains the pattern, I can do the same…
You are wondering why I would like to keep the XL file…for few reasons…I need to keep track of time of the inventory…and I do this on the worksheet name…and each work sheet represents a cluster…so, if I find duplicate Mac addresses for the same cluster the script will ignore the old inventory and execute some commands . If there duplicate from diffrent clusters, then I would abort the process for that particular MAC address.
I will try to incorporate your sciprt into mine. So, when I locate the worksheet which contains the MAC address, do I need to convert it into CSV? and how do I go on to load it inot the $Stones variable in your code pls?
Thank you very much.
Regards,
Boulbul
I
No. I don’t.
IMHO Even for those reasons it would be easier with CSV files.
I guess you will figure that out by yourself. And you don’t have to call the variable $Stones … that would be in fact counterproductive since you should use meaningful variable names.
Here’s something more to read for you:
Bonjour Olaf,
I came to my senses and dropped the XL module for this script. You are right, it can be done much easier.
I have adopted your model above at first, it was easy, but then run into few problems until I found something different and simpler I think.
Here is my code:
#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 (*.xlsx)|*.xlsx'
}
$null = $VMSourceFileBrowser.ShowDialog()
## Convert each worksheet in the excel file into a csv file.
#find each sheet in the workbook and convert into
$sheets = (Get-ExcelSheetInfo -Path $VMSourceFileBrowser.FileName).Name
## read each sheet and create a CSV file with the same name
foreach ($sheet in $sheets) {
Import-Excel -WorksheetName $sheet -Path $VMSourceFileBrowser.FileName| Export-Csv "./$sheet.csv" -NoTypeInformation
}
#Search into a CSV file for Mac Adress
clear
$MACAddresses = Get-Content -Path ($MacFileBrowser).FileName
$User = $env:USERPROFILE
$CsvFiles = (dir *.csv).Name
ForEach ($MACAddresse in $MACAddresses) {
$n= (Get-Content $CsvFiles| select-string -pattern $MACAddresse).length
if ($n -gt 1)
{
Write-host " "
Write-host " "
Write-host "########################################################################################"
Write-Warning "[$n] x references are found for Mac Address [$MACAddresse] on the spreadsheet!!!!"
Write-Warning "The corresponding VM will be ignored. Please take a note of the MAC Address"
Write-host "########################################################################################"
Write-host " "
Write-host " "
}
else
{
Write-host " "
Write-host " "
Write-host "####################################################################################################"
Write-Host "[$n] x instance(s) of MAC Address [$MACAddresse] found the following GU Server or Cluster:"
Write-host "---------------------------------------------------------------------------------------------"
Write-host "[$($CsvFile)].The corresponding VM will be restarted soon. Please confirm the VM details beforehand"
Write-host "####################################################################################################"
Write-host " "
Write-host " "
#Restart-VM ($VMname).VMName -force
#restart-vm -vmname DC -force
#Search into a CSV file for Mac Adress
(Select-string $CsvFiles -pattern $MACAddresse |Select-Object *).line
}
}
and here is the output
####################################################################################################
[1] x instance(s) of MAC Address [AAAAAAAAAAAA] found the following GPU Server or Cluster:
---------------------------------------------------------------------------------------------
[SRV1-2022-09-06-04-40.csv].The corresponding VM will be restarted soon. Please confirm the VM details beforehand
####################################################################################################
"SRV-HostA","Microsoft Windows Server 2019 Standard","192.168.1.100","fe80::d37b:0::da1","TestVMA","AAAAAAAAAAAA","04/02/2021 19:56:00","112","d41a6622-1298-4988-9e42-ad5f0530413e","100","Boulbul"
########################################################################################
AVERTISSEMENT : [2] x references are found for Mac Address [BBBBBBBBBBBB] on the spreadsheet!!!!
AVERTISSEMENT : The corresponding VM will be ignored. Please take a note of the MAC Address
########################################################################################
########################################################################################
AVERTISSEMENT : [3] x references are found for Mac Address [CCCCCCCCCCCC] on the spreadsheet!!!!
AVERTISSEMENT : The corresponding VM will be ignored. Please take a note of the MAC Address
########################################################################################
####################################################################################################
[1] x instance(s) of MAC Address [DDDDDDDDDDDD] found the following GPU Server or Cluster:
---------------------------------------------------------------------------------------------
[SRV1-2022-09-06-04-40.csv].The corresponding VM will be restarted soon. Please confirm the VM details beforehand
####################################################################################################
"SRV-HostD","Microsoft Windows Server 2019 Datacenter","192.168.1.103","fe80::d37b:0::da4","TestVMD","DDDDDDDDDDDD","04/02/2021 19:43:37","116","14ed3aaf-dfeb-4ac8-8a31-efce22711f64","200","Boulbul"
From the command
select-string $CsvFiles -pattern $MACAddresse
I could not select an object from this command… I checked with get-member and I got the following:
TypeName : Microsoft.PowerShell.Commands.MatchInfo
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
RelativePath Method string RelativePath(string directory)
ToString Method string ToString(), string ToString(string directory)
Context Property Microsoft.PowerShell.Commands.MatchInfoContext Context {get;set;}
Filename Property string Filename {get;}
IgnoreCase Property bool IgnoreCase {get;set;}
Line Property string Line {get;set;}
LineNumber Property int LineNumber {get;set;}
Matches Property System.Text.RegularExpressions.Match[] Matches {get;set;}
Path Property string Path {get;set;}
Pattern Property string Pattern {get;set;}
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
RelativePath Method string RelativePath(string directory)
ToString Method string ToString(), string ToString(string directory)
Context Property Microsoft.PowerShell.Commands.MatchInfoContext Context {get;set;}
Filename Property string Filename {get;}
IgnoreCase Property bool IgnoreCase {get;set;}
Line Property string Line {get;set;}
LineNumber Property int LineNumber {get;set;}
Matches Property System.Text.RegularExpressions.Match[] Matches {get;set;}
Path Property string Path {get;set;}
Pattern Property string Pattern {get;set;}
I checked Select-Object
select-string $CsvFiles -pattern $MACAddresse | Select-Object *
I got this
IgnoreCase : True
LineNumber : 5
Line : "SRV-HostD","Microsoft Windows Server 2019 Datacenter","192.168.1.103","fe80::d37b:0::da4","TestVMD","DDDDDDDDDDDD","04/02/2021 19:43:37","116","14ed3aaf-dfeb-4ac8-8a31-efce22711f64","200","Boulbul"
Filename : Boubul-2022-09-06-04-42.csv
Path : C:\Users\nabil.adamou\Boubul-2022-09-06-04-42.csv
Pattern : DDDDDDDDDDDD
Context :
Matches : {0}
And
$Result.Line
Yields the following
"SRV-HostD","Microsoft Windows Server 2019 Datacenter","192.168.1.103","fe80::d37b:0::da4","TestVMD","DDDDDDDDDDDD","04/02/2021 19:43:37","116","14ed3aaf-dfeb-4ac8-8a31-efce22711f64","200","Boulbul"
The question is, how make custom objects from $Result , knowing that every string is separated by a comma?
Thanks in advance.
Regards,
Boulbul
Obviously you did not drop the Excel module.
In my opinion that was a bad idea. If you have structured data you should use the advantages they offer. Select-String is made for plain text files - not CSV files.
That’s why I think it was a bad idea. You solved one problem but you’ve got two others more.
I’d recommend to enter your questions into your favorite search engine at first.