Ping IP Address Residing In a Cell

charlesa920 · Dec 10th 2007

I have a local area network with a couple of hundred computers which share one internet connection. Each user has a CPE (customer premise equipment) with an assigned (by me) IP. This CPE connets to the users equipment via CAT5. My responsibility is to provide internet. Sometimes users call and say their comupter cannot access the internet. I need a quick way to see if the problem lies with my network or if the problem is with the customer's computer.

It seems to me that if I could open my spreedsheet with all the network connections and user data and simply ping their IP I would see if the problem is with my equipment or the users.

Column A of my spreedsheet has the actual IP addresses: 192.168.1.1 thru 192.168.1.254 and 10.0.0.1 thru 10.0.0.254 but not all are currently being used. Each row has distinct user account information.

I have created a shortcut, named it PING106.bat and listed the target as %windir%\system32\ping.exe 192.168.1.106 which I can click on and it runs. Next I inserted a hyperlink in A:107 and it does work (it brings up a DOS screen and pings 192.168.1.106 three times then closes the DOS screen)... But there must be a better way. I don't want to create hundreds of shortcuts and insert hyperlinks to specific cells one at a time.

It would be nice if I could click on a cell which contains an IP and know if that particular IP is up and reachable on my LAN.

Thanks for any ideas,
Charles

BTW as a heads up. If this PINGING can be done the next step is to output something which indicates if the link is up or down. the next step is to conditionally formatt the cell green/red. and to automate the process to run every couple of minutes.... but that is the stuff for another thread...

shg · Dec 10th 2007

Re: ping a cell value which is an IP address

This will loop through the IP addresses in column A and color-code the cell to indicate internet access. You could adapt it to test a double-clicked cell.

Code

Option Explicit


Private Declare Function GetRTTAndHopCount Lib "iphlpapi.dll" _
        (ByVal iDestIPAddr As Long, _
         ByRef iHopCount As Long, _
         ByVal iMaxHops As Long, _
         ByRef iRTT As Long) As Long
         
Private Declare Function inet_addr Lib "wsock32.dll" _
        (ByVal cp As String) As Long


Sub TestPings()
    Dim cell As Range
    Dim astr() As String
    
    For Each cell In Intersect(ActiveSheet.UsedRange, Columns(1))
        astr = Split(cell.Value, ".")
        If UBound(astr) = 3 Then
            cell.Select
            cell.Interior.ColorIndex = xlNone
            cell.Interior.ColorIndex = IIf(Ping(cell.Text, 20), 4, 3)
        End If
    Next
End Sub


Public Function Ping(sIPadr As String, iMaxHops As Long) As Boolean


    ' Based on an article on CodeGuru by Bill Nolde
    ' Implemented in VBA in Nov 2002 by G. Wirth, Ulm,  Germany
    
    Const SUCCESS   As Long = 1
    
    Dim iIPadr      As Long
    Dim iHopCount   As Long
    Dim iRTT        As Long
    
    iIPadr = inet_addr(sIPadr)
    Ping = (GetRTTAndHopCount(iIPadr, iHopCount, iMaxHops, iRTT) = SUCCESS)
    
    Debug.Print "IP Address ....... " & iIPadr & vbLf _
              & "HopCount ......... " & iHopCount & vbLf _
              & "Round trip, ms ... " & iRTT
End Function

Display More

**mikerickson** · Dec 10th 2007

Re: ping a cell value which is an IP address

Theres a good reference at your cross post, http://vbaexpress.com/forum/showthread.php?t=16606

**Reafidy** · Dec 10th 2007

Re: ping a cell value which is an IP address

Thanks Mike.

Charelsa920,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

SHG - Nice Solution, shame the op has wasted everyones time by seeking a solution on multiple sites.

shg · Dec 10th 2007

Re: ping a cell value which is an IP address

Quote

SHG - Nice Solution

To be clear, I invented very little of it ...

Ping IP Address Residing In a Cell

Participate now!

Share