View Full Version : excel connexion
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
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!
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
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
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.
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.
you can download my file on http://www.guif.net/armaris.xls
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.