Excel VBA CMD to List Directory Contents

Question

How would I modify this Excel VBA to:

List the paths of the directories and not just the files?
Filter out system and hidden files?

Here is the the current VBA:

Sub MM()
    Dim fResults As Variant
    fResults = GetFiles("C:\Temp")
    Range("A1").Resize(UBound(fResults) + 1, 1).Value = _ 
    WorksheetFunction.Transpose(fResults)
End Sub


// UDF to populate array with files, assign to a Variant variable.
Function GetFiles(parentFolder As String) As Variant
    GetFiles = Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & parentFolder & _
        IIf(Right(parentFolder, 1) = "\", vbNullString, "\") & "*.*"" /S /B /A:-D").StdOut.ReadAll, vbCrLf), ".")
End Function

Is FSO not an option? you could get list of files and folders using that — Zac
– Zac, Commented Mar 22, 2019 at 16:25
Have a look at stackoverflow.com/a/14911969/7599798 to see how to traverse through all subdirs using the FilesystemObject. Instead of counting the file sizes, you could fill a collection or dictionary. — FunThomas
– FunThomas, Commented Mar 22, 2019 at 16:28
I have a directory with almost 10,000 folders/files I want to list, and I found CMD markedly faster. — BadDogTitan
– BadDogTitan, Commented Mar 22, 2019 at 16:30

Ron Rosenfeld · Accepted Answer · 2019-03-23 11:42:27Z

2

Your Filter … IIF … construct is also removing the directories. Get rid of it. Something like

GetFiles = Split(CreateObject("WScript.Shell").Exec("cmd /c dir """ & parentFolder & """ /A-H-S /B /S").StdOut.ReadAll, vbNewLine)

might better do what you want

answered Mar 23, 2019 at 11:42

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BadDogTitan Over a year ago

Fantastic, Ron. Does exactly what I want. Is there a way to hide the CLI window when calling?

Ron Rosenfeld Over a year ago

@BadDogTitan If you do an internet search, you'll find some ideas having to do with Windows API calls. I've not been able to get them to work satisfactorily in the past, so I usually use the .Run method with the xlHidden argument; redirect the output to a temporary file; read the file using the FileSystemObject, then delete the file. You may have to make some adjustments if you have characters with code > 255 in your data. IIRC correctly, you'll need a \U flag for the CMD call and with the FSO set TristateTrue

Ben Ootjers · Accepted Answer · 2019-03-23 07:29:47Z

2

The answer is in changing the parameters for the dir command.

Use:

DIR /S /B /A-H-S

The -D currently specified hides the directories. Full explanation of the dir parameters can be found here https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/dir

answered Mar 23, 2019 at 7:29

Ben Ootjers

3531 silver badge15 bronze badges

Collectives™ on Stack Overflow

Excel VBA CMD to List Directory Contents

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related