CODEWITHSUNDEEP
excelvbacmd
BadDogTitan
BadDogTitan

Reputation: 103

Excel VBA CMD to List Directory Contents

How would I modify this Excel VBA to:

  1. List the paths of the directories and not just the files?
  2. 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

Upvotes: 1

Views: 1931

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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

Upvotes: 2

Ben Ootjers
Ben Ootjers

Reputation: 353

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

Upvotes: 2

Related Questions