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

Randy Birch has a technique here http://vbnet.mvps.org/index.html?code/internet/ping.htm

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

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

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]

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)

Same versions as me.

All the declaration stuff must be BEFORE any procedures.

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

I came up with another way in this thread http://vbaexpress.com/forum/showthread.php?t=16612

http://im1.shutterfly.com/procserv/4...0AbsWjRk1ZOWOA
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

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]

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

The mod works for me

It appears I can be obtuse.

Of course it works! My bad!

Similar thread, might be helpful for thread..

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

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

That is two people today who have questionned my code. I'll get a complex at this rate.

Isn't 192.168 router addresses?

I thought first octet was network, second octet was router, or network identifier..?

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

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?

xlt, yes:
I have one static IP assigned by my ISP that all the users share.
So when I am using this VBA code it is to ping only the users that are on my network.

Now if a user calls and says they can't get on the internet I can quickly determine if:
I am able to reach them via my network
IF yes THEN the problem is in their location
ELSE I need to run down my backhauls and APs to see they are up
OR I need to verify my internet connection is up

My spreedsheet has the IP for every CPE, Backhaul, & AP on my network, and the static IP we use to surf.

I should be in pretty good shape now.

As you have pointed out on so many occassions...
nothing is perfect... and I'm sure managment will ask for more.

NAT - Network Address Translation, converts private class B / A network address's to a common public address.
Not all address's are split on the octet, it's just lazy humans who assume they are (cos it's easier for us) any point on the 32 bit (until we all go IPv6 and then it'll be a hell of a lot more !) segment can be used as a sub-net identifier, though it would be kinda pointless if it were the last :) the firewall / router bit is irrelevant, it may just be that his 'smoothwall' contains a dhcp server and a NAT device, why buy three bricks when you can buy the one ?

Quote:

---

Originally Posted by charlesa920

xlt, yes:
I have one static IP assigned by my ISP that all the users share.
So when I am using this VBA code it is to ping only the users that are on my network.

Now if a user calls and says they can't get on the internet I can quickly determine if:
I am able to reach them via my network
IF yes THEN the problem is in their location
ELSE I need to run down my backhauls and APs to see they are up
OR I need to verify my internet connection is up

My spreedsheet has the IP for every CPE, Backhaul, & AP on my network, and the static IP we use to surf.

I should be in pretty good shape now.

As you have pointed out on so many occassions...
nothing is perfect... and I'm sure managment will ask for more.

---

Maybe not perfect, but a nice practicval application. Good luck with it.