excel connexion

[guif]

Hi!
I see in this blog an example to make some pings. But... I would like to make another thing.
See my case:

I have a button in excel ("start_" and "stop_") and I have two cells.
-in cell A1 i have a name of computer or an IP.
-in cell B1 i would like to make the result.

If i have a click in the button, i would like to make a "ping -t host_name" and put the result "on line" or "off line" in the cell B1.

Is it possible??
very thank's!

[Bob Phillips]

See the other thread http://vbaexpress.com/forum/showthread.php?t=16606

[guif]

Yes, i see this post yesterday but I don't understand...
It's possible to put the code into a VBA in the excel?

oh! it's very important.. If the computer disconnected the B1 puts to Off line and if it's reconnected puts On line.

[Bob Phillips]

That is VBA code!

[guif]

actually I use this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim sPingcmd As String
    On Error GoTo ErrorTrap
    If Intersect(Target, Range("E3:E238")).Address = Target.Address Then
        sPingcmd = "ping -t " & Target.Value
        Call Shell("cmd /K" & sPingcmd, vbNormalFocus)
    End If
ErrorTrap:
End Sub

If i have a press the IP, it's run de msdos and make a continuos ping but, i can't put a color (Green or red) in the next column. Is it possible to make this with my code?

[Bob Phillips]

Try this

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sPingcmd As String
Dim status As Variant

On Error GoTo ErrorTrap
If Intersect(Target, Range("E3:E238")).Address = Target.Address Then

If Target.Value <> "" Then

status = CreateObject("WScript.Shell"). _
Exec("%comspec% /c Ping -n 1 -w 750 " & Target.Value).StdOut.ReadAll

If InStr(status, "TTL=") = 0 Then
Target.Interior.ColorIndex = 3
Else
Target.Interior.ColorIndex = 4
End If
End If
End If
ErrorTrap:
End Sub
[/vba]

[guif]

can you explain me this line???

Exec("%comspec% /c Ping -n 1 -w 750 " & Target.Value).StdOut.ReadAll

if I put a "-t" the excel is close.

Exec("%comspec% /c Ping -t " & Target.Value).StdOut.ReadAll

[Bob Phillips]

What do you mean, the Excel is close?

[Bob Phillips]

Usage: ping [-t] [-a] [-n count] [-l size] [-f] [-i TTL] [-v TOS][-r count] [-s count] [[-j host-list] | [-k host-list]][-w timeout] destination-list

Options:
-t Ping the specifed host until interrupted.
-a Resolve addresses to hostnames.
-n count Number of echo requests to send.
-l size Send buffer size.
-f Set Don't Fragment flag in packet.
-i TTL Time To Live.
-v TOS Type Of Service.
-r count Record route for count hops.
-s count Timestamp for count hops.
-j host-list Loose source route along host-list.
-k host-list Strict source route along host-list.
-w timeout Timeout in milliseconds to wait for each

[guif]

If I put your perfect code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sPingcmd As String
Dim status As Variant

On Error Goto ErrorTrap
If Intersect(Target, Range("E3:E238")).Address = Target.Address Then

If Target.Value <> "" Then

status = CreateObject("WScript.Shell"). _
Exec("%comspec% /c Ping -n 1 -w 750 " & Target.Value).StdOut.ReadAll

If InStr(status, "TTL=") = 0 Then
Target.Interior.ColorIndex = 3
Else
Target.Interior.ColorIndex = 4
End If
End If
End If
ErrorTrap:
End Sub
It's make an only one ping and put the color. Very good, but the ping is not continuos and it's possible that the conexion of computer that close.
The colour is not real in this case....

[Bob Phillips]

post a workbook, and we'll see what we can do.

[guif]

IP STATE
172.17.41.37 ?
172.17.40.253 ?
172.17.41.4 ?
172.17.40.132 ?
172.17.40.182 ?
172.17.40.166 ?

If I click on the IP, the CMD it's run and make a ping continuos and in the state i would like to put the colors green/red
with the first code it's possible to make a "ping -t" but isn't possible to put the colurs in the State cell.
If i put the second code, I see the colours, but the CMD is close.

Not's possible make a continuos ping and refresh the colour?

[Bob Phillips]

Can't you post a workbook, with proper data proper sheet names, it saves me much time.

[guif]

you can download my file on http://www.guif.net/armaris.xls