PDA

View Full Version : excel connexion



guif
12-10-2007, 10:03 PM
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
12-11-2007, 01:35 AM
See the other thread http://vbaexpress.com/forum/showthread.php?t=16606

guif
12-11-2007, 02:14 AM
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
12-11-2007, 03:33 AM
That is VBA code!

guif
12-11-2007, 03:58 AM
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
12-11-2007, 04:20 AM
Try this



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

guif
12-11-2007, 04:57 AM
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
12-11-2007, 05:07 AM
What do you mean, the Excel is close?

Bob Phillips
12-11-2007, 05:16 AM
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
12-12-2007, 09:35 AM
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
12-12-2007, 09:44 AM
post a workbook, and we'll see what we can do.

guif
12-13-2007, 12:17 AM
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
12-13-2007, 12:50 AM
Can't you post a workbook, with proper data proper sheet names, it saves me much time.

guif
12-13-2007, 12:57 AM
you can download my file on http://www.guif.net/armaris.xls