Can an Excel macro pull output from a command line

Question

I have a VBA macro that executes a Command Line function to create a text file listing the files in a directory; however, instead of copying and pasting/importing the list from the text file to the worksheet, is it possible to simply have the putput of the command line be passed directly into the worksheet?

The VBA code I am working with right now to create the file is:

CMD = "DIR /A:-D-H /O:-D-G /B \\directory\* > \\sleuth.txt"
Shell "cmd.exe /c " & CMD

I much appreciate any ideas on this front!

Answer 1

You can save it to a string variable using this class. (Direct download link here.)

Here's an example usage:

Sub Test()
    Dim cls As New clsRunApp
    Dim s As String
    cls.command = "cmd.exe /k dir"

    s = cls.RunAppWait_CaptureOutput
    Set cls = Nothing
    MsgBox (s)
End Sub

From there, you can output it to your worksheet directly.