VB script to get IP address from Ping command

Question

I'm trying to write a script to show every server ipaddress that I put into a text file. I've been looking online and came across the script below. What I need is instead of it showing 'online' I need it show show the actual IP address of each server in the text file. I've been looking for an answer to this for a while now, I've pretty new to vbs so I'm sorry if the script below is wrong or simple. This does open an excel doc which I'm pretty happy with.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2

objExcel.Cells(1, 1).Value = "Server Name"
objExcel.Cells(1, 2).Value = "IP Address"

Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("MachineList.Txt")

Do While Not (InputFile.atEndOfStream)
HostName = InputFile.ReadLine

Set WshShell = WScript.CreateObject("WScript.Shell")
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)

objExcel.Cells(intRow, 1).Value = HostName

Select Case Ping
Case 0 objExcel.Cells(intRow, 2).Value = "On Line"
Case 1 objExcel.Cells(intRow, 2).Value = "Off Line"
End Select

intRow = intRow + 1
Loop

objExcel.Range("A1:B1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit 

Answer 1

Edited because my original statement wasn't accurate. You can get the StdOut of a process launched with exec like this:

Option Explicit

Const HOST_FILE = "MachineList.txt"

Dim shl, exe, exl, fso, file
Dim iRow, out, host

Set shl = CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FilesystemObject")
Set exl = CreateObject("Excel.Application")

exl.Workbooks.Add
iRow = 2
exl.Cells(1,1).Value = "Server Name"
exl.Cells(1,2).Value = "IP Address"

Set file = fso.OpenTextFile(HOST_FILE)

While not file.AtEndOfStream
  host = Trim(file.ReadLine)

  exl.Cells(iRow,1).Value = host  

  Set exe = shl.Exec("%COMSPEC% /c ping -n 1 """ & host & """ | Find ""statistics for""")

  If Not exe.StdOut.AtEndOfStream Then
    out = exe.StdOut.ReadAll
    exl.Cells(iRow,2).Value = getIP(out)
  Else
    exl.Cells(iRow,2).Value = "Ping Failed"
  End If  

  iRow = iRow + 1
Wend

exl.Visible = True
Set exl = Nothing
Set shl = Nothing
Set fso = Nothing
Set exe = Nothing
WScript.Quit

Function getIP(text)
  Dim s

  s = Mid(text, Len("Ping statistics for ") + 1)
  getIP = Trim(Replace(s,":",""))
End Function

However, the exec function has no WindowStyle option, so you'll see the command processor flash up for every time it runs ping.

Answer 2

You can use the RUN method of the script shell instead and have the ping statement output to a text file. Then read the text file once the ping statement completes and get the info that way.

Set objWSH = CreateObject("WScript.Shell")
objWSH.Run "%COMSPEC% /c ping -n 1 """ & host & """ | Find ""statistics for"" > temp.txt", 0, True
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("temp.txt", 1)
out = Trim(objFile.ReadAll)
If out <> "" Then
    ' Read ping data
Else
    ' Ping failed to run
End If

Or something along those line. That should get you on the right track.