2

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
    'Stop
    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
            cmd.Terminate
            MsgBox "Error: Timed Out", vbCritical, "Timed Out"
        End If
    Loop

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

wshError:
    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") or RunCMD("winrs -r:" & strHost & " reg query hklm\system\currentcontrolset\services\cdrom /v start")

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

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?

Community
  • 1
  • 1
Tim
  • 2,701
  • 3
  • 26
  • 47
  • 1
    This probably doesn't address the primary failure but shouldn't you be piping you output to a TXT file to be opened and manipulated after the operation has completed? –  Jun 14 '16 at 21:08
  • 1
    Have you tried it with `Set oShell = CreateObject("Cscript.Shell")` ? –  Jun 14 '16 at 21:10
  • it's actually giving the error `The system cannot find the file specified.` – Brad Jun 14 '16 at 21:44
  • Do you need to use command line tools/is this just and example? or Can you use the FileSystemObject which can do this much much nicer. – Brad Jun 14 '16 at 21:44
  • Along with @Brad comment, [VBA has DIR](http://www.techonthenet.com/excel/formulas/dir.php) command as well. – Sgdva Jun 14 '16 at 21:50
  • http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba – Brad Jun 14 '16 at 21:59
  • @Jeeped Yes, I suppose if speedy and efficient code weren't an issue, I could create an FSO, pipe the cmd text to a file, open the file, parse the text, close the file, then delete the file. And so long as nothing goes wrong, I shouldn't end up with some random text file. I've never tried creating a cscript shell. I'm not even sure its possible, but I'll give it a shot. – Tim Jun 15 '16 at 04:05
  • @Brad It's kind of an example. I was answering a different SO question about finding files amongst hundred of directories and sub directories when I had this issue. VBA's `DIR` function won't work. It doesn't search subdirectories. CMD's `DIR` function has command line switches that will quickly and efficiently produce a list of files that match a set of criteria (do `Dir c:\config* /a:-d /b /d /s` from cmd to find every file on your system that starts with config) . You could write a VBA equivalent that would recurse subdirectories to compile the list, but it wouldn't be nearly as efficient. – Tim Jun 15 '16 at 04:13

1 Answers1

3

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""")

or 

RunCmd("cmd /c ""Dir c:\config* /a:-d /b /d /s""")
dbmitch
  • 5,361
  • 4
  • 24
  • 38