Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ping in excel
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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ping in excel
what do you mean by "ping"?
try selection.value = Cells(selection.row,1).Value Patrick Molloy Microsoft Excel MVP "Andy Mohan" wrote in message ... 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 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ping in excel
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 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ping in excel
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" wrote in message ... 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 ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ping in excel
This is all the code you will need:
That's ALL? <vbg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ping in excel
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" wrote in message ... This is all the code you will need: That's ALL? <vbg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PING within Excel Cell | Excel Worksheet Functions | |||
get data from CMD programs directly into excel e.g. PING | Excel Discussion (Misc queries) | |||
PING Bob Phillips | Excel Discussion (Misc queries) | |||
VBA Query coding in Ms Excel for Mac OS 9 (ping Jim) | Excel Programming | |||
Ping anybody | Excel Programming |