This VBS script will allow you to read a list of machine names contained in an Excel spreadsheet called MachineList.xls. It will then write the IP address for the machine name in column A to column B.
To use this script the C:\ MachineList.xls file must exist with a list of machine names one per line beginning with cell A2 as cell A1 and B1 are reserved for the column header information that you can specify as you wish. For my purposes I have labeled A1 as “Machine Name” and B1 as “IP Address” and the B column of course is not populated until the script is executed.
Note: You can change the spreadsheet location in the script to the location and file name of your choice such as H:\MyFile.Xls or even \\ServerName\ShareName\FileName.Xls as needed.
VBS Script:
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\MachineList.xls")
objExcel.Visible = True
i = 2
Do Until objExcel.Cells(i, 1).Value = ""
strComputer = objExcel.Cells(i,1)
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
For Each objItem in colItems
If Not IsNull(objItem.IPAddress) Then
For Each objIPAddress in objItem.IPAddress
objExcel.Cells(i,2) = objIPAddress
Next
End If
i = i + 1
Loop
objExcel.Cells.EntireColumn.AutoFit
MsgBox "Done"
No Comments