Running Ping from Excel

[Donnie Stone]

Is it possible to ping a list of IP addresses and display the results in a
spreadsheet?

The IP addresses are in a1:a30. I would like to ping each IP and capture
the results in the workbook.

Secondly, I would like to execute the routine every 30 minutes.

Thanks in advance for the help.

Donnie

[Bob Phillips]

Donnie,

If you go to Randy Birch's site at www.mvps.org/vbnet there is an example
there of 'How to Ping an IP address using Visual Basic'. Randy's example
returns it to a form, but it would be easy to adapt it to pick up IP
Addresses from a spreadsheet and return results to same.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

"Donnie Stone" <Donni...@carolina.rr.com> wrote in message
news:ucLPj2DK...@TK2MSFTNGP11.phx.gbl...

[Bob Kilmer]

Not very sophisticated, but I couldn't help but give it a try. <g>

Option Explicit

Sub Ping()
Dim wkb As Workbook, t0 As Single
Set wkb = Nothing
'(could loop thru IP range, etc.)
Shell ("cmd /c ping 64.58.79.230 > t:\ping.txt")
On Error Resume Next
t0 = Timer()
Do
Set wkb = Workbooks.Open("t:\ping.txt")
If Timer() - t0 > 5 Then Exit Do
If Not wkb Is Nothing Then Exit Do
Loop
On Error goto 0
'If Not wkb is Nothing Then ...
'could copy relevant lines to other wkb
'then close wkb, kill ping.txt, loop, etc.
End If
End Sub

Bob Kilmer

"Donnie Stone" <Donni...@carolina.rr.com> wrote in message
news:ucLPj2DK...@TK2MSFTNGP11.phx.gbl...

[Donnie Stone]

Bob,

Thanks. BTW, is it possible to list the IP addresses in the Ping.txt file
and have the IPs pinged in ascending order.

Also, how do I know if the ping was successful?

Donnie

"Bob Kilmer" <rpr...@yahoo.com> wrote in message
news:%23KOgCtH...@TK2MSFTNGP11.phx.gbl...

[Milos Setek]

Hello Donnie,

You could use the VBA shell command to run a batch file where you could put
this DOS command

ping machine_name > machine_name_ping_result_file

Then just open the created file (machine_name_ping_result_file) in VBA, read
the results and put in a workbook.

Then schedule the VBA program from another DOS batch program using the
windows scheduler.

Regards,
Milos.

"Donnie Stone" <Donni...@carolina.rr.com> wrote in message
news:ucLPj2DK...@TK2MSFTNGP11.phx.gbl...

[Bob Kilmer]

Donnie,
Well, the ping.txt file I created using the shell command was the output of
the ping, but yes, you could have an input text file containing IP
addresses, open the file, read 'em and insert them in the cmd string.

You know if the ping is executed if the code exits the loop with "Not wkb Is
Nothing" being true - that is, if a workbook has been assigned to wkb. If
wkb Is Nothing, then the workbook.Open ("t:\ping.txt") failed, presumably
because the ping.txt file did not get created. It would be important to
delete the ping.txt file for each new ping, or use unique names for these
files if you were going to rely on file creation as a measure of success.
(You could check to see if the file got created before you try to open the
workbook, but then, there are a lot of ways to arrange these things to get
the job done.)

The ping returns a limited number of predictable message formats. Learn what
they are is. Learn to parse them. For example

C:\>ping 0.0.0.0
Unknown host 0.0.0.0.

or

C:\>ping gateway
Unknown host gateway.

or
(partially)

Reply from 66.218.71.198: bytes=32 time=140ms TTL=240

Ping statistics for 66.218.71.198:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 78ms, Maximum = 140ms, Average = 101ms

among a few others . (You can search the text for field identifiers like
TTL=, Packets:, Sent = , Received =, etc., to identify data.)

Milos Setek posted a good idea - create a batch file (you could even create
it in code, if that is called for) then execute the batch file, look for the
resulting output files, open the files, parse the files, copy needed data.

Since they are text files, you don't really need to open the files in Excel,
per se, just use the VBA Open statement ('Open "t:\ping064.058.079.230.txt"
for Input as #fn'). (See Open, Input, Input #, Print #, Close in help or do
a Google search if you are not familiar with reading text files using Open.)

This approach is a little seat-of-the-pants, meaning potentially less
reliable than the approach illustrated on VBNet of using "real" system
functions to get what you need. There is a little guess-work involved if a
ping doesn't result in a file, and you are relying on the ping message
format being predictable. You will have to judge whether this approach will
meet your needs. I think it has potential.

Bob Kilmer

"Donnie Stone" <Donni...@carolina.rr.com> wrote in message

news:Ol09pGKK...@TK2MSFTNGP10.phx.gbl...