Excel code for bulk URL Ping

[savindrasingh]

Hello Experts,

Please find below code to ping multiple IP Addresses using Excel.

Option Explicit
Sub PingTest()
Dim URL, IPAddr As String, SiteName As String, i As Integer
Dim URLs As Range, objShell, objCommand, strCommand, strPingResult, arrIPAddress, strIPAddress
If Range("A" & Rows.Count).End(xlUp).Row <= 1 Then
    MsgBox "No URLs listed under Column 'A'," & vbCrLf & "Input URLs and try again.", vbCritical, "Missinng Input"
    Exit Sub
End If
Set URLs = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Set objShell = CreateObject("WScript.Shell")
'ping -n 1 -w 300 atgprod.wideip.ml.com | Findstr /B /C:"Reply from"
i = 0
For Each URL In URLs
    URL.Offset(0, 2) = "Processing.."
    URL.Offset(0, 2).Interior.Color = 14922893
    strCommand = "CMD /C Ping -n 1 -w 300 " & URL & " | Findstr /B /C:" & Chr(34) & "Reply from" & Chr(34)
    Set objCommand = objShell.Exec(strCommand)
    strPingResult = objCommand.StdOut.ReadAll
    If strPingResult <> "" Then
        arrIPAddress = Split(strPingResult, ":")
        strIPAddress = Mid(arrIPAddress(0), 12)
        URL.Offset(0, 1).Value = strIPAddress
        URL.Offset(0, 2) = "Done"
        URL.Offset(0, 2).Interior.Color = 5296274
    Else
        URL.Offset(0, 1).Value = "NA"
        URL.Offset(0, 2) = "Failed"
        URL.Offset(0, 2).Interior.Color = 255
    End If
    i = i + 1
    If i >= 46 Then ActiveWindow.SmallScroll Down:=1
    URL.Select
Next
MsgBox "Task Completed." & vbCrLf & i & " URLs processed", vbInformation, "Done"
End Sub

You need to add this code to a new Module in VBA. Then put the list of servers on Column 'A' starting from row 2.

When you will run this macro, results will be displayed under Column 'B' and 'C'

 

[tlowry]

Nicely done.

Is it too much to ask to not see the "Command" window. Although, it does remind me of my youth.

 

[BretLark]

Hi,

Could you please tell me how to make this work for a particular port? I have to ping a large number of devices on a public IP address with a custom port. I have another script that does this but not as good as this one.

Many thanks in advance.

Bret