Hi,

I have some VBA script that I am using to ping a list of IP addresses and provide a result in another column. It works almost exactly as I want it to except I need to ping a port and not an IP due to my devices all being remote. The script also provides the tested IP address in column B and I would this to be a hyperlink so that I can browse directly to the device.

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, "Missing 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

Private Sub CommandButton1_Click()
     Sheet1.PingTest
End Sub

Many thanks in advance.

Bret

• Moved by Emi Zhang CHNMicrosoft contingent staff 5 hours 8 minutes ago Move Case

I need to ping a port

Have a look here:

http://serverfault.com/questions/309357/ping-a-specific-port

Andreas.

Hi Bret,

This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for VBA.
https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba 

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

Emi Zhang
TechNet Community Su

My apologies for confusing people. I thought the fact that I am using VBA "within" Excel was a good reason to post in an Excel forum.