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.