How to execute a command in VBA

[Dirk]

I have a spreadsheet with some ip addresses in it that I would link to issue a "ping" command for, parse the results and put the results in a different worksheet. I am able to use WScript to execute the command and get the results back in variable but there is a very annoying window that pops up andthen disappears. I assume that it is the window that comes up when you run"cmd" from the Windows start button.

Is there a way to not have the window come up. If I use run I can not get the output into the variable.

Thanks

 

[Claus Busch]

Hi Dirk,

Am Thu, 11 Apr 2013 10:48:41 -0700 (PDT) schrieb Dirk:

I have a spreadsheet with some ip addresses in it that I would link to issue a "ping" command for, parse the results and put the results in a different worksheet. I am able to use WScript to execute the command and get the results back in variable but there is a very annoying window that pops up and then disappears. I assume that it is the window that comes up when you run "cmd" from the Windows start button.

please have a look:
http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/e59a38e1-eaf0-4b13-af10-fd4be559f50f/


Regards
Claus Busch

 

[Dirk]

This is the code that I have and that works. If I use the "run" option I get a type mismatch error.

Option Explicit
Sub test_function()

Dim var_1 As String
Dim var_2 As String

var_1 = "192.168.10.1"
var_2 = get_info(var_1)
Debug.Print var_2

End Sub

Function get_info(ip As String) As String

Dim obj_wsh
Dim obj_run
Dim var_cmd
Dim var_result As String
Dim var_lines() As String

Set obj_wsh = CreateObject("WScript.Shell")
var_cmd = "ping -n 4 -w 30 " & ip

' Set obj_run = obj_wsh.Run("%ComSpec% /c " & var_cmd & "| > nul 2>&1", 0, True)
Set obj_run = obj_wsh.exec("%ComSpec% /c " & var_cmd)
var_result = obj_run.StdOut.ReadAll
var_lines = Split(var_result, vbCrLf)
get_info = var_lines(1)

End Function