Tim Tim - 1 year ago 132
Bash Question

Can't run DIR from WScript Shell in VBA?

I use the following function in a lot of my VBA projects. I initially added the reference to Windows Script Host Object model to take advantage of Intellisense, but then switched to late binding so I didn't have to reference a bunch of stuff.

Private Function RunCMD(ByVal strCMD As String) As String
'Runs the provided command
Dim oShell As Object 'New WshShell
Dim cmd As Object 'WshExec
Dim x As Integer
Const WshRunning = 0

On Error GoTo wshError

x = 0
RunCMD = "Error"
Set oShell = CreateObject("Wscript.Shell")
Set cmd = oShell.Exec(strCMD)
'Debug.Print strCMD
Do While cmd.Status = WshRunning
Sleep 100 'for 1/10th of a second
x = x + 1
If x > 1200 Then 'We've waited 2 minutes so kill it
MsgBox "Error: Timed Out", vbCritical, "Timed Out"
End If

RunCMD = cmd.StdOut.ReadAll & cmd.StdErr.ReadAll
Set oShell = Nothing
Set cmd = Nothing
Exit Function

On Error Resume Next
RunCMD = cmd.StdErr.ReadAll
Resume Next
End Function

It works great when you do something like
RunCMD("ping www.bing.com")
RunCMD("winrs -r:" & strHost & " reg query hklm\system\currentcontrolset\services\cdrom /v start")

RunCMD("Dir c:\config* /a:-d /b /d /s")
fails, and
gives an Object Variable or With Block not set error. Even a simple

Why does DIR make the WScript shell crap out? More importantly, how can I use CMD's DIR function (not VBA's DIR function!) to get a list of files that match a search pattern?

Answer Source

Does it work if you preface your dir command with "cmd /c " and wrap your DOS command in double quotes, like

RunCmd("cmd /c ""DIR""")


RunCmd("cmd /c ""Dir c:\config* /a:-d /b /d /s""")