Search in a workbook and retrieve relevant details in XL

[boulbul]

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

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.

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

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