Excel Interaction with Command Prompt

[cbzdmh]

Hi

I'm trying to get Excel to run a command from the command propmpt to see if a PC (named in cell A1 is pingable.

Is this possible?

If so how do i get the results back into Excel?

Many Thanks

 

[Richard Schollar]

Hi

You can certainly use the Shell function to run the shell. I'm not sure about returning results to Excel though - I can imagine you could do it by outputting the results of the ping to a text file, and using VBA to open the text file and extract the contents.

Shell "ping www.google.com", vbNormalFocus

 

[RoryA]

You can ping using API functions - does your cell contain the IP address or the host name?

 

[cbzdmh]

I've kind of solved it, the code needs tidying up, but this works.


Sub ping()
Dim PC As String
Dim Pings, timeout As Integer
Dim Ping_Result As String
PC = "PCNAME"

Pings = 1
timeout = 100

Status = CreateObject("WScript.Shell"). _
Exec("%comspec% /c Ping -n " & Pings & " -w " & timeout & " " & PC).StdOut.ReadAll

If InStr(Status, "TTL=") = 0 Then
Ping_Result = " Doesn't Ping"
Else
Ping_Result = " Pings"
End If

MsgBox PC & Ping_Result

End Sub

 

[nbrcrunch]

Instead of using the msgbox, (and assuming you have more than one computer to test), you can feed the return to the adjacent column. The only thing I'm uncertain about is whether you'd need to built a delayed timing loop between each run.

Sub ping()
Dim PC As String
Dim Pings, timeout As Integer
Dim Ping_Result As String

For looper = 1 To 1000
If Range("A" & looper).Value = "" Then Exit Sub

PC = Range("A" & looper).Value

Pings = 1
timeout = 100

Status = CreateObject("WScript.Shell"). _
Exec("%comspec% /c Ping -n " & Pings & " -w " & timeout & " " & PC).StdOut.ReadAll

If InStr(Status, "TTL=") = 0 Then
Ping_Result = " Doesn't Ping"
Else
Ping_Result = " Pings"
End If

Range("B" & looper) = PC & Ping_Result
Next looper
End Sub

 

[cbzdmh]

I just use msgbox as a way of checking I have an output I'm happy with before feeding the value to a cell.

Is there anyway of running all the commands using the same cmd window. Currently when i try running this for 20 machines, it opens (and shuts) 20 cmd windows. Or even hiding the CMD window?