# Search in a workbook and retrieve relevant details in XL

**URL:** <https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412>\
**Category:** PowerShell Help\
**Created:** [September 13, 2022, 8:59pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412 "2022-09-13T20:59:58Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 13, 2022, 8:59pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/1 "2022-09-13T20:59:58Z")

</div>

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](https://mcpmag.com/articles/2018/04/06/find-excel-data-with-powershell.aspx)

```auto

#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

```auto

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
```

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [September 13, 2022, 9:11pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/2 "2022-09-13T21:11:48Z")

</div>

> [@boulbul](#):
>
> ```auto
> If you have any otherway which seem to be easier to search in a workbook with powershell, please do not hesitate to suggest it.
> 
> ```

May I ask why you’re not using the specialized module [ImportExcel](https://www.powershellgallery.com/packages/ImportExcel)? If I remember right you already used it, don’t you? 🤔 😉

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 13, 2022, 9:16pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/3 "2022-09-13T21:16:34Z")

</div>

Yes I did. I thought that the function above works with the same module?..I guess I did not read well. 😵‍💫

Thank you.

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 14, 2022, 2:26pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/5 "2022-09-14T14:26:23Z")

</div>

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:

```auto
$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:

```auto

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.

```auto
Worksheet
---------

```

Any help please.

Thank you in advance.

Kind regards,

Boulbul

---

<div class="post-metadata">

**Author:** ![krzydoug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/krzydoug/32/1008_2.png) [@krzydoug](https://forums.powershell.org/u/krzydoug)\
**Post date:** [September 14, 2022, 4:48pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/6 "2022-09-14T16:48:51Z")

</div>

What does the following command show?

```auto
$output | Get-Member

```

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 14, 2022, 6:25pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/7 "2022-09-14T18:25:45Z")

</div>

Thanks for getting back to me.

The output of

> [@krzydoug](#):
>
> `$output | Get-Member`

is

```auto

   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

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [September 14, 2022, 9:32pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/8 "2022-09-14T21:32:57Z")

</div>

> [@boulbul](#):
>
> The output of
> 
> > [@krzydoug](#):
> >
> > `$output | Get-Member`
> 
> is  
> …

This is unfartunately not helpful at all … and here is why …

Look at the output of this:

```auto
Get-Process | Select-Object -First 1

```

Now we want to know what methods and properties the outputted object has. We do this:

```auto
Get-Process | Select-Object -First 1 | Get-Member

```

But when you use a format cmdlet like `Format-Table`:

```auto
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 …

> [@boulbul](#):
>
> search for a string in a workbook

Do you really have to search the whole workbook? Or can you narrow the area you want to search? Maybe to a specific column …

---

<div class="post-metadata">

**Author:** ![krzydoug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/krzydoug/32/1008_2.png) [@krzydoug](https://forums.powershell.org/u/krzydoug)\
**Post date:** [September 14, 2022, 10:36pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/9 "2022-09-14T22:36:40Z")

</div>

Yeah I totally missed the format command. If that is removed the initial select command should work as expected

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 15, 2022, 10:56am UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/10 "2022-09-15T10:56:56Z")

</div>

Hi Olaf,

Thanks for getting back to me. …

> [@Olaf](#):
>
> Do you really have to search the whole workbook? Or can you narrow the area you want to search? Maybe to a specific column …

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](https://forums.powershell.org/t/how-to-retrieve-vms-details-from-multiple-clusters/20342/2)

[Export the output of a script into a new worksheet in an exsiting file in XL](https://forums.powershell.org/t/export-the-output-of-a-script-into-a-new-worksheet-in-an-exsiting-file-in-xl/20345)

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.

> [@krzydoug](#):
>
> Yeah I totally missed the format command. If that is removed the initial select command should work as expected

Yes, it works now!!!..Thank you Krzydoug/Olaf…wonderfulll…so, glad.

Now the script output looks like this:

```auto
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

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [September 15, 2022, 11:13am UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/11 "2022-09-15T11:13:48Z")

</div>

> [@boulbul](#):
>
> 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](https://forums.powershell.org/t/how-to-retrieve-vms-details-from-multiple-clusters/20342/2)
> 
> [Export the output of a script into a new worksheet in an exsiting file in XL](https://forums.powershell.org/t/export-the-output-of-a-script-into-a-new-worksheet-in-an-exsiting-file-in-xl/20345)
> 
> 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.

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.

> [@boulbul](#):
>
> 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?

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. 🤷🏽‍♂️

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 15, 2022, 11:41am UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/12 "2022-09-15T11:41:44Z")

</div>

HI again Olaf,

> [@Olaf](#):
>
> 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.

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?

```auto

[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

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [September 15, 2022, 12:11pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/13 "2022-09-15T12:11:59Z")

</div>

> [@boulbul](#):
>
> I am not so sure if I understood your message…Cannot see the difference between the excel and csv…but am interested to know

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. 🤷🏽‍♂️

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 15, 2022, 1:03pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/14 "2022-09-15T13:03:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 15, 2022, 2:37pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/15 "2022-09-15T14:37:06Z")

</div>

Olaf,

Sorry to bother yoiu again on this …

> [@boulbul](#):
>
> ```auto
> [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 locations are…should I use the PSEXcel (Function Get-CellValue to solve this problem? or with Import-excel module…or just with powershell commands?

[PSExcel 1.0](https://www.powershellgallery.com/packages/PSExcel/1.0/Content/Get-CellValue.ps1)

Thanks

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [September 15, 2022, 2:45pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/16 "2022-09-15T14:45:58Z")

</div>

> [@boulbul](#):
>
> should I use the PSEXcel (Function Get-CellValue to solve this problem? or with Import-excel module

I don’t know. 🤷🏽‍♂️

> [@boulbul](#):
>
> I know. where the locations are

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.

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 16, 2022, 2:37pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/17 "2022-09-16T14:37:54Z")

</div>

Olaf,

I

> [@Olaf](#):
>
> I think you’re overcomplicating this a lot.

,  
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:

```auto
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

```auto
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

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [September 16, 2022, 3:16pm UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/18 "2022-09-16T15:16:49Z")

</div>

> [@boulbul](#):
>
> but have no choice

Sorry. But this is bullshit. 🙄

Here’s a little example of what I mean:

```auto
$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.

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 17, 2022, 7:18am UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/19 "2022-09-17T07:18:02Z")

</div>

Olaf,

> [@Olaf](#):
>
> Sorry. But this is bullshit. 🙄

You are right…it is not about choice but about time rather…

> [@Olaf](#):
>
> Here’s a little example of what I mean:
> 
> ```auto
> 
> ```

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.

> [@Olaf](#):
>
> ```auto
> $Stones = @'
> FirstName,LastName,Instrument
> Mick,Jagger,voice
> Ron,Wood,guitar
> Keith,Richards,guitar
> Charlie,Watts,drums
> '@ |
> ConvertFrom-Csv
> 
> ```

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

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [September 17, 2022, 8:48am UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/20 "2022-09-17T08:48:36Z")

</div>

> [@boulbul](#):
>
> You are wondering why I would like to keep the XL file…

No. I don’t.

> [@boulbul](#):
>
> …for few reasons…

IMHO Even for those reasons it would be easier with CSV files.

> [@boulbul](#):
>
> 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?

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:

> **[GitHub - PoshCode/PowerShellPracticeAndStyle: The Unofficial PowerShell Best...](https://github.com/PoshCode/PowerShellPracticeAndStyle)**
>
> The Unofficial PowerShell Best Practices and Style Guide - GitHub - PoshCode/PowerShellPracticeAndStyle: The Unofficial PowerShell Best Practices and Style Guide

> **[KISS principle](https://en.wikipedia.org/wiki/KISS_principle)**
>
> KISS, an acronym for "Keep it simple, stupid!", is a design principle first noted by the U.S. Navy in 1960. First seen partly in American English by at least 1938, the KISS principle states that most systems work best if they are kept simple rather than made complicated; therefore, simplicity should be a key goal in design, and unnecessary complexity should be avoided. The phrase has been associated with aircraft engineer Kelly Johnson. The term "KISS principle" was in popular use by 197 The acro...

---

<div class="post-metadata">

**Author:** ![boulbul](https://avatars.discourse-cdn.com/v4/letter/b/a183cd/32.png) [@boulbul](https://forums.powershell.org/u/boulbul)\
**Post date:** [September 21, 2022, 8:34am UTC](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412/23 "2022-09-21T08:34:00Z")

</div>

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:

```auto
#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

```auto
  
 
####################################################################################################
[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

```auto
 select-string $CsvFiles -pattern $MACAddresse 

```

I could not select an object from this command… I checked with get-member and I got the following:

```auto
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`

```auto
select-string $CsvFiles -pattern $MACAddresse | Select-Object *

```

I got this

```auto
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

```auto
 $Result.Line

```

Yields the following

```auto
"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

[Next page](https://forums.powershell.org/t/search-in-a-workbook-and-retrieve-relevant-details-in-xl/20412.md?page=2)