Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
Hi
Is it possible to ping a range of local IP addresses in VBA code by looping through the range using variables to produce a text file which includes the results? The range I want to ping is 192.168.0.0 to 192.168.255.255 I can create the nested loop command structure to increment the variables but can't pass them to the ping command. Extract of the code: Option Explicit Dim a,b,c,d,y,z For y = 0 To 255 For z = 0 To 255 Let a = 192 Let b = 0 Let c = y Let d = z Shell ("cmd /c ping a.b.c.d c:\ping.txt") Next z Next y This results in the following message being pasted to ping.txt: "Ping request could not find host a.b.c.d. Please check the name and try again" from which I assume the contents of the variables are not being passed into the shell command. Assuming the variables can be passed to the ping command, is it then possible to append the results of each loop to the text file instead of over writing it's contents? The reason I want to do this is that I have a printer with a NIC that I want to connect to my router and I think the printer has a fixed IP address but I can't communicate with it and don't want to spend eternity manually pinging all the addresses in the range until I hit the right one! Unfortunately I don't have the manual for the NIC and the Kyocera website isn't much help (not even sure the NIC card is a Kyocera). Any suggestions appreciated. Thanks Garry Douglas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
You've put the variables between the quotes so they're being read literally.
Best to declare a string variable and concatenate: strPing="cmd /c ping" & a & "." & b & "." etc Shell (strPing) Re appending to a text file, I'm a bit rusty on that but VBA help is OK - try looking up "Write #" "Garry Douglas" wrote: Hi Is it possible to ping a range of local IP addresses in VBA code by looping through the range using variables to produce a text file which includes the results? The range I want to ping is 192.168.0.0 to 192.168.255.255 I can create the nested loop command structure to increment the variables but can't pass them to the ping command. Extract of the code: Option Explicit Dim a,b,c,d,y,z For y = 0 To 255 For z = 0 To 255 Let a = 192 Let b = 0 Let c = y Let d = z Shell ("cmd /c ping a.b.c.d c:\ping.txt") Next z Next y This results in the following message being pasted to ping.txt: "Ping request could not find host a.b.c.d. Please check the name and try again" from which I assume the contents of the variables are not being passed into the shell command. Assuming the variables can be passed to the ping command, is it then possible to append the results of each loop to the text file instead of over writing it's contents? The reason I want to do this is that I have a printer with a NIC that I want to connect to my router and I think the printer has a fixed IP address but I can't communicate with it and don't want to spend eternity manually pinging all the addresses in the range until I hit the right one! Unfortunately I don't have the manual for the NIC and the Kyocera website isn't much help (not even sure the NIC card is a Kyocera). Any suggestions appreciated. Thanks Garry Douglas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
Hi everybody,
I think, the OP will know by now about numbers and strings and about how to build the command string for the shell. Anyway, here is one more elaborate example. Not the shortest or best way to do it, for sure, it is just to make it all clear. Sub MyPing() Dim a As String Dim b As String Dim c As String Dim d As String Dim y As Long Dim z As Long Dim MyCmd As String a = "192." b = "0." For y = 0 To 255 c = CStr(y) c = c & "." For z = 0 To 255 d = CStr(z) MyCmd = "cmd /c ping " & a & b & c & d & " c:\test\ping.txt" ' seems to work MyCmd = "cmd /c ping 99.113.11.8 c:\test\ping.txt" ' for testing with an existing IP ' though I've changed the number for the posting ;-) Shell (MyCmd) Next z Next y End Sub Like Vista Small Business it set up here, it is not possible to write into the root directory c:\, therefore I was writing to c:\test\... Furthermore, the OP might have to wait until the shell command has written the textfile. Google for shellx, xshell, and others, like http://vb.mvps.org/hardcore/html/usingshell.htm http://vb.mvps.org/samples/project.asp?id=Shell32 For appending text to a textfile, use "". -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Vista Small Business, Office XP |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
"Helmut Weber" wrote in message
... Hi everybody, I think, the OP will know by now about numbers and strings and about how to build the command string for the shell. Anyway, here is one more elaborate example. Not the shortest or best way to do it, for sure, it is just to make it all clear. I may have known about numbers and strings once upon a time but it's been over a year since I wrote any code and my brain has atrophied in the meantime! I couldn't even remember how to open the Immediate window this morning. Like every other muscle, you've got to use it or lose it. Thanks for the suggestions and the code. I will post back here when I find the version that works best for me. Garry |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
Thanks.
I tried the following: Str = "192.168." & c & "." & d Open "TESTFILE" For Output As #1 Write #1, Shell("cmd /c ping " & Str & " C:\ping2.txt") Close #1 ' Close file. Stepping through the code, on the first iteration the routine pasted 2544 in Testfile, second time round it was 7368. I'm not sure what these numbers represent but whatever the values are they do not appear to be appended to the file. I assume that this is because I am closing and then re-opening the file on each pass. I looked at Help but it doesn't :-) Garry "Smallweed" wrote in message ... You've put the variables between the quotes so they're being read literally. Best to declare a string variable and concatenate: strPing="cmd /c ping" & a & "." & b & "." etc Shell (strPing) Re appending to a text file, I'm a bit rusty on that but VBA help is OK - try looking up "Write #" "Garry Douglas" wrote: Hi Is it possible to ping a range of local IP addresses in VBA code by looping through the range using variables to produce a text file which includes the results? The range I want to ping is 192.168.0.0 to 192.168.255.255 I can create the nested loop command structure to increment the variables but can't pass them to the ping command. Extract of the code: Option Explicit Dim a,b,c,d,y,z For y = 0 To 255 For z = 0 To 255 Let a = 192 Let b = 0 Let c = y Let d = z Shell ("cmd /c ping a.b.c.d c:\ping.txt") Next z Next y This results in the following message being pasted to ping.txt: "Ping request could not find host a.b.c.d. Please check the name and try again" from which I assume the contents of the variables are not being passed into the shell command. Assuming the variables can be passed to the ping command, is it then possible to append the results of each loop to the text file instead of over writing it's contents? The reason I want to do this is that I have a printer with a NIC that I want to connect to my router and I think the printer has a fixed IP address but I can't communicate with it and don't want to spend eternity manually pinging all the addresses in the range until I hit the right one! Unfortunately I don't have the manual for the NIC and the Kyocera website isn't much help (not even sure the NIC card is a Kyocera). Any suggestions appreciated. Thanks Garry Douglas |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
Hi Garry,
from the help: If the Shell function successfully executes the named file, it returns the task ID of the started program. Which doesn't help you much... I'm not sure what these numbers represent but whatever the values are they do not appear to be appended to the file. No, for appending use "". Then you don't have to worry at all, about "open for", "close", "write", whatsoever. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
This may help
Dim a As Long, b As Long, c As String Dim y As Long, z As Long For y = 0 To 255 For z = 0 To 255 Let a = 192 Let b = 0 c = a & "." & b & "." & y & "." & z Shell ("cmd /c ping " & c & " c:\ping.txt") Next z Next y -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Garry Douglas" wrote in message ... Hi Is it possible to ping a range of local IP addresses in VBA code by looping through the range using variables to produce a text file which includes the results? The range I want to ping is 192.168.0.0 to 192.168.255.255 I can create the nested loop command structure to increment the variables but can't pass them to the ping command. Extract of the code: Option Explicit Dim a,b,c,d,y,z For y = 0 To 255 For z = 0 To 255 Let a = 192 Let b = 0 Let c = y Let d = z Shell ("cmd /c ping a.b.c.d c:\ping.txt") Next z Next y This results in the following message being pasted to ping.txt: "Ping request could not find host a.b.c.d. Please check the name and try again" from which I assume the contents of the variables are not being passed into the shell command. Assuming the variables can be passed to the ping command, is it then possible to append the results of each loop to the text file instead of over writing it's contents? The reason I want to do this is that I have a printer with a NIC that I want to connect to my router and I think the printer has a fixed IP address but I can't communicate with it and don't want to spend eternity manually pinging all the addresses in the range until I hit the right one! Unfortunately I don't have the manual for the NIC and the Kyocera website isn't much help (not even sure the NIC card is a Kyocera). Any suggestions appreciated. Thanks Garry Douglas |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
I missed the bit about the text file.
You might consider this alternative approach http://www.vbaexpress.com/forum/show...highlight=ping -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... This may help Dim a As Long, b As Long, c As String Dim y As Long, z As Long For y = 0 To 255 For z = 0 To 255 Let a = 192 Let b = 0 c = a & "." & b & "." & y & "." & z Shell ("cmd /c ping " & c & " c:\ping.txt") Next z Next y -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Garry Douglas" wrote in message ... Hi Is it possible to ping a range of local IP addresses in VBA code by looping through the range using variables to produce a text file which includes the results? The range I want to ping is 192.168.0.0 to 192.168.255.255 I can create the nested loop command structure to increment the variables but can't pass them to the ping command. Extract of the code: Option Explicit Dim a,b,c,d,y,z For y = 0 To 255 For z = 0 To 255 Let a = 192 Let b = 0 Let c = y Let d = z Shell ("cmd /c ping a.b.c.d c:\ping.txt") Next z Next y This results in the following message being pasted to ping.txt: "Ping request could not find host a.b.c.d. Please check the name and try again" from which I assume the contents of the variables are not being passed into the shell command. Assuming the variables can be passed to the ping command, is it then possible to append the results of each loop to the text file instead of over writing it's contents? The reason I want to do this is that I have a printer with a NIC that I want to connect to my router and I think the printer has a fixed IP address but I can't communicate with it and don't want to spend eternity manually pinging all the addresses in the range until I hit the right one! Unfortunately I don't have the manual for the NIC and the Kyocera website isn't much help (not even sure the NIC card is a Kyocera). Any suggestions appreciated. Thanks Garry Douglas |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
"Bob Phillips" wrote in message
... I missed the bit about the text file. You might consider this alternative approach http://www.vbaexpress.com/forum/show...highlight=ping Thanks. I love the way there is never one absolutely definitive way of achieving the end result. For a non-programmer this is very reassuring :-) Garry |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping via VBA
http://www.rlmueller.net/PingComputers.htm
At this link you can find three routines that ping remote computers, depending on your Windows version. The code is VBScript, but I think it would be easy to convert it to VBA. -- urkec "Garry Douglas" wrote: "Bob Phillips" wrote in message ... I missed the bit about the text file. You might consider this alternative approach http://www.vbaexpress.com/forum/show...highlight=ping Thanks. I love the way there is never one absolutely definitive way of achieving the end result. For a non-programmer this is very reassuring :-) Garry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ping a Set of IPs | Excel Programming | |||
Ping from Excel - Almost there | Excel Programming | |||
PING Bob Phillips | Excel Discussion (Misc queries) | |||
ping in excel | Excel Programming | |||
Ping anybody | Excel Programming |