Ping excel script and get ip reply

[mowael]

Hello,

I have a Hostname List and I need a result = ip (172,217,19,132)

I tried to search on it but everyone is talking about time response, so I need to know how to search on a document.

Part of a script down there is getting ( result + Response time + TTL )_ im sure these belong to the liberary that added in the script beginning "objping"
I tried to search on the package, but I got no results, my thoughts are that there's other results it can get, so if i read what this package can get I can get all results I need ? so how

 

[Bosquedeguate]

Here is some code that will give you the ping results.
I hope this helps you.

Sub Test_Connection()
  i = 1
  'While (ActiveSheet.Cells(i, 1) <> "")
    'Retrieve IP address
    strCompAddress = ActiveSheet.Cells(i, 1)  'IP Address in Cell A1
    
    '//Setup Shell command for Ping
    '//NOTE: Make to leave a space after PING.exe
    Dim strShellCommand As String
    strShellCommand = "C:\Windows\System32\PING.exe -a " + strCompAddress
    
    '//Create Shell Object in to run Scripts
    Set oSh = CreateObject("WScript.Shell")
    Set oEx = oSh.Exec(strShellCommand)
    
    '//Read output buffer
    strbuf = oEx.StdOut.ReadAll
    ActiveSheet.Cells(i, 9) = strbuf
    ActiveSheet.Cells(i, 8) = Split(strbuf, ",")(2)
    ActiveSheet.Cells(i, 7) = Split(strbuf, " ")(1)
  'Wend
End Sub

This code does some parsing of the Ping Results

Sub SplitPingResult()
  Dim tmp, x, vals
  Dim icolon, iequal
  tmp = Split(Range("B1"), vbLf)
  For x = 0 To UBound(tmp)
    Cells(x + 2, 1) = tmp(x)
    
    icolon = InStr(tmp(x), ": ")
    If icolon > 0 Then
      Cells(x + 2, 2) = Left(tmp(x), icolon - 1) 'Reply from x.x.x.x
      Cells(x + 2, 3) = Mid(tmp(x), icolon + 2) 'bytes, time, TTL
      vals = Split(Cells(x + 2, 3), ", ")
      If UBound(vals) = 0 Then vals = Split(Cells(x + 2, 3), " ")
      If UBound(vals) > 0 Then
        Cells(x + 2, 4) = vals(0)
        Cells(x + 2, 5) = vals(1)
        Cells(x + 2, 6) = vals(1)
      End If
    End If
  Next x
End Sub

Sample of partially parsed Ping Results data

Book3
	A	B	C	D	E	F
1	10.0.1.1	Pinging S4HSOTANO [10.0.1.1] with 32 bytes of data: Reply from 10.0.1.1: bytes=32 time=1ms TTL=255 Reply from 10.0.1.1: bytes=32 time=1ms TTL=255 Reply from 10.0.1.1: bytes=32 time<1ms TTL=255 Reply from 10.0.1.1: bytes=32 time=1ms TTL=255 Ping statistics for 10.0.1.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 1ms, Average = 0ms		Bytes	Time	TTL
2	Pinging S4HSOTANO [10.0.1.1] with 32 bytes of data:
3	Reply from 10.0.1.1: bytes=32 time=1ms TTL=255	Reply from 10.0.1.1	bytes=32 time=1ms TTL=255	bytes=32	time=1ms	time=1ms
4	Reply from 10.0.1.1: bytes=32 time=1ms TTL=255	Reply from 10.0.1.1	bytes=32 time=1ms TTL=255	bytes=32	time=1ms	time=1ms
5	Reply from 10.0.1.1: bytes=32 time<1ms TTL=255	Reply from 10.0.1.1	bytes=32 time<1ms TTL=255	bytes=32	time<1ms	time<1ms
6	Reply from 10.0.1.1: bytes=32 time=1ms TTL=255	Reply from 10.0.1.1	bytes=32 time=1ms TTL=255	bytes=32	time=1ms	time=1ms
7
8	Ping statistics for 10.0.1.1:
9	Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),	Packets	Sent = 4, Received = 4, Lost = 0 (0% loss),	Sent = 4	Received = 4	Received = 4
10	Approximate round trip times in milli-seconds:
11	Minimum = 0ms, Maximum = 1ms, Average = 0ms
Sheet1