Excel VBA List Files in Folder – Dir in Shell

List Files in Folder using Excel VBA

To get list of files in a directory, just give the folder path as input to this function. It will read the file in the folder one by one. Then display the file names.

In this sample code, we will get list of Excel files using wild card *.xls. If you want to list all text files, then use *.txt. For listing all files use *.*.

1.List files using VBA.Dir function

Note: Avoid creating any file inside the while loop in same folder. This might cause an endless loop.

Sub ListFilesInFolder(sFolderPath As String)
    'Define variable name & types (officetricks)
    Dim sFileName
    Dim sPathSeperator As String: sPathSeperator = Application.PathSeparator
    If VBA.Right(sFolderPath, 1) <> sPathSeperator Then sFolderPath = sFolderPath & sPathSeperator
        
    'Get list of Excel files in folder
    sFileName = VBA.Dir(sFolderPath & "*.xls*", vbNormal)
    
    'Loop Until all files are processed
    While sFileName <> ""
        'Print File Name
        Debug.Print sFileName
        
        'Get next file name
        sFileName = VBA.Dir
    Wend
    
    'Process Completed
    MsgBox "All Files in folder are processed"
End Sub

In the above sample code, the output is directed to Immediate Window. This can be viewed by using the short cut Ctrl + G.

This while loop will automatically read each file name at a time. Once all the files are read & listed, the VBA.Dir command will return empty string. Hence the loop will terminate & exit.

2. Files List using Shell & Cmd Dir

This code execute the dos command dir & captures its output to a variable. Then display the output by splitting list of files in each line. The output is displayed in immediate window as above.

Here is the code with Shell command.

Sub listFilesUsingShell(sFolderPath As String)
    Dim vFileName
    Dim sFileName
    Dim fNum
    
    'Execute DIR command in Shell
    vFileName = VBA.Split(CreateObject("WScript.Shell").Exec("cmd /c dir """ & parentFolder & """ /B").StdOut.ReadAll, vbNewLine)
    
    'Loop Thru each files
    fNum = 0
    For Each sFileName In vFileName
        fNum = fNum + 1
        If sFileName <> "" Then Debug.Print fNum & ": " & sFileName
    Next
End Sub

The actual syntax for the Dir dos command is “Dir folderpath /B” .

The ‘/B” switch actually display only the file names. Other wise the dir command will display file with path details & also attributes like file date, size etc.,

External Reference: I would have settled with first method of using VBA.Dir command. Until I stumbled upon this discussion in stackoverflow. This discussion also gave an idea on how to capture the output from a Shell execute command. I really learnt this technique along with code for Excel vba list files in folder.