Piping the output of a Shell Command executed in VBA to a Specific Shell

Question

I am still very very new to VBA and started learning it a couple days ago. Now I am trying to create a Macro to execute a shell command and pipe the output to a specific cell in a specific worksheet. What I am trying to accomplish is to get a text dump of the directory structure into a worksheet. Below is the code i have so far.

Sub CopyList()

    Call Shell("cmd.exe /S /K" & "dir /s /b directoryPath", vbNormalFocus)

End Sub

Executing this macro brings up a command prompt and dumps the directory structure inside the cmd window. I was wondering how I could pipe this to a worksheet. Your help would be greatly appreciated.

Answer 1

You can create the WScript.Shell object and read the StdOut directly:

Sub SO()

Range("A1").Value = CreateObject("WScript.Shell").Exec("CMD /S /C dir /s /b directoryPath").StdOut.ReadAll

End Sub