Run DIR CMD From Macro

BadDogTitan

New Member
Joined
Sep 16, 2013
Messages
18
The command
Code:
dir /S /B /A:-S > FileList.xls
lists every folder, subfolder and file except for system files on a worksheet. I want the user to be able to select a folder, and obtain this result without having to run a command prompt.

Is there a way to run this command from VBA?

Thanks.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Welcome to the forum! While you could use Shell() to do that, it might be slow and not fully fill the text file before other code ran. Of course sending ascii test to a binary file would not work.

Maybe this example will better suit your goal. Change the command line switches and such to suit.
Code:
Sub DirStdOut()
  Dim s As String, a() As String
  ' /b = bare file listing, /s = search subfolders, /c = open command shell, run command and then close shell.
  ' /a:-d means list files only and not subfolders
  s = CreateObject("Wscript.Shell").exec("cmd /c dir x:\test\*.* /a:d /b").StdOut.ReadAll
  a() = Split(s, vbCrLf)
  With Range("A1")
    .EntireColumn.Clear
    .Resize(UBound(a)).Value = WorksheetFunction.Transpose(a)
  End With
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,098
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top