0

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!

Jason L
  • 1
  • 1
  • 1
  • You can certainly have VBA open the text file, read it in and then write it out to your worksheet. – RBarryYoung Apr 26 '13 at 15:34
  • 2
    I think you're asking the wrong question - rather than trying to get the output of the shell command directly into the worksheet, why not just get the directory listing directly within VBA rather than shelling out in the first place? See eg the answers to http://stackoverflow.com/questions/3017318/vba-getting-list-of-all-files-with-esy-extension-in-a-directory – Vicky Apr 26 '13 at 15:43
  • RBarry young - I can definitely do that, but I figure if I can take out a few steps why not? Vicky, that is a very good idea, though I am not entirely sure how to get subfolders to be included in searches (not pertinent for this current task, but there are others that it would be important). I will dig around here to see if I can find an example of someone doinf that. Thank you! – Jason L Apr 26 '13 at 17:28
  • Or just poke around with the functions - yay! – Jason L Apr 26 '13 at 17:33
  • http://support.microsoft.com/kb/185601/EN-US – Siddharth Rout Apr 26 '13 at 18:16

1 Answers1

0

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.

Peter Rankin
  • 713
  • 1
  • 6
  • 29