Microsoft Excel Webinar

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 23

Thread: Solved: ping a cell value which is an IP address

  1. #1

    Solved: ping a cell value which is an IP address

    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...

    I had a bit of a brain Fa-- and posted this same topic on another forum but I do not have permission to post a link to that site. Fortunately others have posted it for me, thanks
    Last edited by charlesa920; 12-10-2007 at 03:23 PM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,853
    Location
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Theres a pretty detailed answer at your cross post,http://www.ozgrid.com/forum/showthread.php?t=80630

  4. #4
    XLD,
    thanks for the link. I had already seen it with the search as I searched and read every topic mentioning IP or PING. Unfortunately I didn't know how to get it to work in Excel, the part about creating a form kind of confused me. sorry

    mikerickson,
    the code you link to seems to work as long as the IPs are up. the first one that is not active hangs Excell "program not responding". Pretty sure it is something I'm doing, but I think I just did a copy and past and then stepped thru with F8 till it quit responding.

    thanks all,
    Charles

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,853
    Location
    It's easy to convert. Not tested, but here it is

    VB:
     
    Option Explicit 
     
     ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     ' Copyright ?1996-2007 VBnet, Randy Birch, All Rights Reserved.
     ' Some pages may also contain other copyrights by the author.
     ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     ' Distribution: You can freely use this code in your own
     '               applications, but you may not reproduce
     '               or publish this code on any web site,
     '               online service, or distribute as source
     '               on any media without express permission.
     ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     
    Private Const IP_SUCCESS As Long = 0 
    Private Const IP_STATUS_BASE As Long = 11000 
    Private Const IP_BUF_TOO_SMALL As Long = (11000 + 1) 
    Private Const IP_DEST_NET_UNREACHABLE As Long = (11000 + 2) 
    Private Const IP_DEST_HOST_UNREACHABLE As Long = (11000 + 3) 
    Private Const IP_DEST_PROT_UNREACHABLE As Long = (11000 + 4) 
    Private Const IP_DEST_PORT_UNREACHABLE As Long = (11000 + 5) 
    Private Const IP_NO_RESOURCES As Long = (11000 + 6) 
    Private Const IP_BAD_OPTION As Long = (11000 + 7) 
    Private Const IP_HW_ERROR As Long = (11000 + 8) 
    Private Const IP_PACKET_TOO_BIG As Long = (11000 + 9) 
    Private Const IP_REQ_TIMED_OUT As Long = (11000 + 10) 
    Private Const IP_BAD_REQ As Long = (11000 + 11) 
    Private Const IP_BAD_ROUTE As Long = (11000 + 12) 
    Private Const IP_TTL_EXPIRED_TRANSIT As Long = (11000 + 13) 
    Private Const IP_TTL_EXPIRED_REASSEM As Long = (11000 + 14) 
    Private Const IP_PARAM_PROBLEM As Long = (11000 + 15) 
    Private Const IP_SOURCE_QUENCH As Long = (11000 + 16) 
    Private Const IP_OPTION_TOO_BIG As Long = (11000 + 17) 
    Private Const IP_BAD_DESTINATION As Long = (11000 + 18) 
    Private Const IP_ADDR_DELETED As Long = (11000 + 19) 
    Private Const IP_SPEC_MTU_CHANGE As Long = (11000 + 20) 
    Private Const IP_MTU_CHANGE As Long = (11000 + 21) 
    Private Const IP_UNLOAD As Long = (11000 + 22) 
    Private Const IP_ADDR_ADDED As Long = (11000 + 23) 
    Private Const IP_GENERAL_FAILURE As Long = (11000 + 50) 
    Private Const MAX_IP_STATUS As Long = (11000 + 50) 
    Private Const IP_PENDING As Long = (11000 + 255) 
    Private Const PING_TIMEOUT As Long = 500 
    Private Const WS_VERSION_REQD As Long = &H101 
    Private Const MIN_SOCKETS_REQD As Long = 1 
    Private Const SOCKET_ERROR As Long = -1 
    Private Const INADDR_NONE As Long = &HFFFFFFFF 
    Private Const MAX_WSADescription As Long = 256 
    Private Const MAX_WSASYSStatus As Long = 128 
     
    Private Type ICMP_OPTIONS 
        Ttl             As Byte 
        Tos             As Byte 
        Flags           As Byte 
        OptionsSize     As Byte 
        OptionsData     As Long 
    End Type 
     
    Private Type ICMP_ECHO_REPLY 
        Address         As Long 
        status          As Long 
        RoundTripTime   As Long 
        DataSize        As Long 'formerly integer
         'Reserved        As Integer
        DataPointer     As Long 
        Options         As ICMP_OPTIONS 
        Data            As String * 250 
    End Type 
     
    Private Type WSADATA 
        wVersion As Integer 
        wHighVersion As Integer 
        szDescription(0 To MAX_WSADescription) As Byte 
        szSystemStatus(0 To MAX_WSASYSStatus) As Byte 
        wMaxSockets As Long 
        wMaxUDPDG As Long 
        dwVendorInfo As Long 
    End Type 
     
    Private Declare Function IcmpCreateFile Lib "icmp.dll" () As Long 
     
    Private Declare Function IcmpCloseHandle Lib "icmp.dll" _ 
    (ByVal IcmpHandle As Long) As Long 
     
    Private Declare Function IcmpSendEcho Lib "icmp.dll" _ 
    (ByVal IcmpHandle As Long, _ 
    ByVal DestinationAddress As Long, _ 
    ByVal RequestData As String, _ 
    ByVal RequestSize As Long, _ 
    ByVal RequestOptions As Long, _ 
    ReplyBuffer As ICMP_ECHO_REPLY, _ 
    ByVal ReplySize As Long, _ 
    ByVal Timeout As Long) As Long 
     
    Private Declare Function WSAGetLastError Lib "wsock32" () As Long 
     
    Private Declare Function WSAStartup Lib "wsock32" _ 
    (ByVal wVersionRequired As Long, _ 
    lpWSADATA As WSADATA) As Long 
     
    Private Declare Function WSACleanup Lib "wsock32" () As Long 
     
    Private Declare Function gethostname Lib "wsock32" _ 
    (ByVal szHost As String, _ 
    ByVal dwHostLen As Long) As Long 
     
    Private Declare Function gethostbyname Lib "wsock32" _ 
    (ByVal szHost As String) As Long 
     
    Private Declare Sub CopyMemory Lib "kernel32" _ 
    Alias "RtlMoveMemory" _ 
    (xDest As Any, _ 
    xSource As Any, _ 
    ByVal nbytes As Long) 
     
    Private Declare Function inet_addr Lib "wsock32" _ 
    (ByVal s As String) As Long 
     
    Public Sub PingIt(ByVal IPAddress As String, ByVal Msg As String) 
         
        Dim ECHO As ICMP_ECHO_REPLY 
        Dim pos As Long 
        Dim success As Long 
        Dim response As String 
         
        If SocketsInitialize() Then 
             
             'ping the IP by passing the address,
             'text to send, and the ECHO structure.
            success = Ping(IPAddress, Msg, ECHO) 
             
             'display the results
            response = "Return Status:" & vbTab & GetStatusCode(success) & vbNewLine & _ 
            "Address (dec):" & vbTab & ECHO.Address & vbNewLine & _ 
            "Round trip time:" & vbTab & ECHO.RoundTripTime & " ms" & vbNewLine & _ 
            "Data packet size:" & vbTab & ECHO.DataSize & " bytes" & vbNewLine 
             
            If Left$(ECHO.Data, 1) <> Chr$(0) Then 
                pos = InStr(ECHO.Data, Chr$(0)) 
                response = response & "Data returned: & vbtab & Left$(ECHO.Data, pos - 1)" & vbNewLine 
            End If 
             
            response = response & "data pointer:" & vbTab & ECHO.DataPointer 
             
            SocketsCleanup 
             
            MsgBox response 
             
        Else 
             
            MsgBox "Windows Sockets for 32 bit Windows " & _ 
            "environments is not successfully responding." 
             
        End If 
         
    End Sub 
     
     
    Private Function Ping(sAddress As String, _ 
        sDataToSend As String, _ 
        ECHO As ICMP_ECHO_REPLY) As Long 
         
         'If Ping succeeds :
         '.RoundTripTime = time in ms for the ping to complete,
         '.Data is the data returned (NULL terminated)
         '.Address is the Ip address that actually replied
         '.DataSize is the size of the string in .Data
         '.Status will be 0
         '
         'If Ping fails .Status will be the error code
         
        Dim hPort As Long 
        Dim dwAddress As Long 
         
         'convert the address into a long representation
        dwAddress = inet_addr(sAddress) 
         
         'if a valid address..
        If dwAddress <> INADDR_NONE Then 
             
             'open a port
            hPort = IcmpCreateFile() 
             
             'and if successful,
            If hPort Then 
                 
                 'ping it.
                Call IcmpSendEcho(hPort, _ 
                dwAddress, _ 
                sDataToSend, _ 
                Len(sDataToSend), _ 
                0, _ 
                ECHO, _ 
                Len(ECHO), _ 
                PING_TIMEOUT) 
                 
                 'return the status as ping succes and close
                Ping = ECHO.status 
                Call IcmpCloseHandle(hPort) 
                 
            End If 
             
        Else 
             'the address format was probably invalid
            Ping = INADDR_NONE 
             
        End If 
         
    End Function 
     
     
    Private Sub SocketsCleanup() 
         
        If WSACleanup() <> 0 Then 
            MsgBox "Windows Sockets error occurred in Cleanup.", vbExclamation 
        End If 
         
    End Sub 
     
     
    Private Function SocketsInitialize() As Boolean 
         
        Dim WSAD As WSADATA 
         
        SocketsInitialize = WSAStartup(WS_VERSION_REQD, WSAD) = IP_SUCCESS 
         
    End Function 
     
     
    Private Function GetStatusCode(status As Long) As String 
         
        Dim Msg As String 
         
        Select Case status 
        Case IP_SUCCESS:               Msg = "ip success" 
        Case INADDR_NONE:              Msg = "inet_addr: bad IP format" 
        Case IP_BUF_TOO_SMALL:         Msg = "ip buf too_small" 
        Case IP_DEST_NET_UNREACHABLE:  Msg = "ip dest net unreachable" 
        Case IP_DEST_HOST_UNREACHABLE: Msg = "ip dest host unreachable" 
        Case IP_DEST_PROT_UNREACHABLE: Msg = "ip dest prot unreachable" 
        Case IP_DEST_PORT_UNREACHABLE: Msg = "ip dest port unreachable" 
        Case IP_NO_RESOURCES:          Msg = "ip no resources" 
        Case IP_BAD_OPTION:            Msg = "ip bad option" 
        Case IP_HW_ERROR:              Msg = "ip hw_error" 
        Case IP_PACKET_TOO_BIG:        Msg = "ip packet too_big" 
        Case IP_REQ_TIMED_OUT:         Msg = "ip req timed out" 
        Case IP_BAD_REQ:               Msg = "ip bad req" 
        Case IP_BAD_ROUTE:             Msg = "ip bad route" 
        Case IP_TTL_EXPIRED_TRANSIT:   Msg = "ip ttl expired transit" 
        Case IP_TTL_EXPIRED_REASSEM:   Msg = "ip ttl expired reassem" 
        Case IP_PARAM_PROBLEM:         Msg = "ip param_problem" 
        Case IP_SOURCE_QUENCH:         Msg = "ip source quench" 
        Case IP_OPTION_TOO_BIG:        Msg = "ip option too_big" 
        Case IP_BAD_DESTINATION:       Msg = "ip bad destination" 
        Case IP_ADDR_DELETED:          Msg = "ip addr deleted" 
        Case IP_SPEC_MTU_CHANGE:       Msg = "ip spec mtu change" 
        Case IP_MTU_CHANGE:            Msg = "ip mtu_change" 
        Case IP_UNLOAD:                Msg = "ip unload" 
        Case IP_ADDR_ADDED:            Msg = "ip addr added" 
        Case IP_GENERAL_FAILURE:       Msg = "ip general failure" 
        Case IP_PENDING:               Msg = "ip pending" 
        Case PING_TIMEOUT:             Msg = "ping timeout" 
        Case Else:                     Msg = "unknown  msg returned" 
        End Select 
         
        GetStatusCode = CStr(status) & "   [ " & Msg & " ]" 
         
    End Function 
    
    
    Formatting tags added by mark007
    test like so

    VB:
     
    Sub test() 
        PingIt "209.68.48.118", "Echo this" 
    End Sub 
    
    
    Formatting tags added by mark007
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    thank you xld
    unfortunately I still cannot get it to run. having same error as when I first came accrossit during my searching...

    when it gets to this line...
    Private Declare Function IcmpCreateFile Lib "icmp.dll" () As Long
    there is an error msg bx
    Compile error:\
    Only comments may appear after End Sub, End Function, or End Property
    Again, I think it is me or my versions. (vba ver 6.3, Excel 2003)

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,853
    Location
    Same versions as me.

    All the declaration stuff must be BEFORE any procedures.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    xld,
    thanks for the clarification. sounds simple when you say it like that.
    I went in and deleted everything in the module, copy and pasted the code. I put the "test" in the code for sheet 3.

    Next I ran the Macro "test" and it resulted in a pop up box with info about the ping being successful, the time, packet size & other cool stuff.

    so I changed the value in the "test" to
    PingIt ActiveCell, "Echo this"
    and it pinged the value in the cell, way cool!

    Thanks so much this is exactly what I need.


    Charles
    Last edited by charlesa920; 12-11-2007 at 07:56 AM.

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,853
    Location
    I came up with another way in this thread http://vbaexpress.com/forum/showthread.php?t=16612
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10

    I was looking at the popup and noticed the IP address is in DEC format.

    The IP it just Pinged is 192.168.1.1 and while I'm okay with the DEC format I am concerned that my user may not be.

    Can anyone suggest an edit of the code to reflect the precise IP depected in the results?
    Thanks
    Charles

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,853
    Location
    I also see a bug there.

    Use this

    VB:
     
     'display the results
    response = "Return Status:" & vbTab & GetStatusCode(success) & vbNewLine & _ 
    "Address (dec):" & vbTab & IPAddress & "(" & ECHO.Address & ")" & vbNewLine & _ 
    "Round trip time:" & vbTab & ECHO.RoundTripTime & " ms" & vbNewLine & _ 
    "Data packet size:" & vbTab & ECHO.DataSize & " bytes" & vbNewLine 
     
    If Left$(ECHO.Data, 1) <> Chr$(0) Then 
        pos = InStr(ECHO.Data, Chr$(0)) 
        response = response & "Data returned:" & vbtab & Left$(ECHO.Data, pos - 1) & vbNewLine 
    End If 
    
    
    Formatting tags added by mark007
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    Yes sir, I see the difference in between the original code I used and the one you are suggesting I use. But after changing the code I see no difference in the output.???

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,853
    Location
    The mod works for me
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    It appears I can be obtuse.

    Of course it works! My bad!

  15. #15
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,835
    Location
    Similar thread, might be helpful for thread..

    http://www.tek-tips.com/viewthread.cfm?qid=1430503

  16. #16
    Thanks to xlt:
    I got both methods working...
    1) shows MsgBox with results
    2) changes cell color to red/green

  17. #17
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,853
    Location
    That is two people today who have questionned my code. I'll get a complex at this rate.

    Isn't 192.168 router addresses?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  18. #18
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,835
    Location
    I thought first octet was network, second octet was router, or network identifier..?

  19. #19
    xld,
    I am using SmoothWall to hand out the IP address to the final users

  20. #20
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,853
    Location
    So is that some kind of firewall product, and all of the IP addresses are localised IPs known only to the firewall? With a single IP address to the outside world?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •