Excel Interaction with Command Prompt

cbzdmh

Board Regular
Joined
Jul 16, 2007
Messages
58
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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.

Code:
Shell "ping www.google.com", vbNormalFocus
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can ping using API functions - does your cell contain the IP address or the host name?
 

cbzdmh

Board Regular
Joined
Jul 16, 2007
Messages
58
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

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

Board Regular
Joined
Jul 16, 2007
Messages
58
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?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top