Excel Hints

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 04:23 PM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,094
    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
    24,094
    Location
    It's easy to convert. Not tested, but here it is

    [vba]

    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
    [/vba]

    test like so

    [vba]

    Sub test()
    PingIt "209.68.48.118", "Echo this"
    End Sub
    [/vba]
    ____________________________________________
    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
    24,094
    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 08:56 AM.

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,094
    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
    24,094
    Location
    I also see a bug there.

    Use this

    [vba]

    '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
    [/vba]
    ____________________________________________
    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
    24,094
    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

  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
    24,094
    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
    24,094
    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
  •