I would like to create an Excel macro to ping a list of numbers in Column A
and put the result in the same row but in the active Column when the macro
started
Any Offers of help ?
I would like to create an Excel macro to ping a list of numbers in Column A
and put the result in the same row but in the active Column when the macro
started
Any Offers of help ?
what do you mean by "ping"?
try
selection.value = Cells(selection.row,1).Value
Patrick Molloy
Microsoft Excel MVP
"Andy Mohan" <[email protected]> wrote in message
news:[email protected]...
>I would like to create an Excel macro to ping a list of numbers in Column A
>and put the result in the same row but in the active Column when the macro
>started
>
> Any Offers of help ?
>
By ping I assume you mean pinging an IP? If so what would you like to do with
the replys? I assume you wanted to catch them somehow.
"Andy Mohan" wrote:
> I would like to create an Excel macro to ping a list of numbers in Column A
> and put the result in the same row but in the active Column when the macro
> started
>
> Any Offers of help ?
>
>
>
This is all the code you will need:
Option Explicit
Private Const IP_SUCCESS As Long = 0
Private Const PING_TIMEOUT As Long = 500
Private Const WS_VERSION_REQD As Long = &H101
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
Function PingComputer(ByVal strIPAddress As String) As Long
Dim ECHO As ICMP_ECHO_REPLY
Dim pos As Long
Dim lResult As Long
Dim str2 As String
If SocketsInitialize() Then
str2 = "test"
'ping the IP by passing the address,
'text to send, and the ECHO structure.
lResult = Ping((strIPAddress), (str2), ECHO)
If Left$(ECHO.Data, 1) <> Chr$(0) Then
pos = InStr(ECHO.Data, Chr$(0))
End If
SocketsCleanup
Else
'MsgBox "Windows Sockets for 32 bit Windows " & _
"environments is not successfully responding."
End If
PingComputer = lResult
End Function
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
Run this like this:
ActiveCell.Value = PingComputer(Cells(ActiveCell.Row, 1).Value)
RBS
"Andy Mohan" <[email protected]> wrote in message
news:[email protected]...
>I would like to create an Excel macro to ping a list of numbers in Column A
>and put the result in the same row but in the active Column when the macro
>started
>
> Any Offers of help ?
>
>This is all the code you will need:
That's ALL? <vbg>
Well, I had a good look for something shorter, but couldn't find it and
settled for this.
Obviously, I didn't write it myself, but just copied the whole lot from
somewhere.
It works perfect.
RBS
"Myrna Larson" <[email protected]> wrote in message
news:[email protected]...
> >This is all the code you will need:
>
> That's ALL? <vbg>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks