vba list all files in folder in Excel
Q185. In Excel, how can I list all files in a folder using VBA?
To do it in Excel, here is the answer:
- Option Explicit
- Sub ListAllFilesInFolder()
- Dim sInputFolderPath As String, sFileName As String
- sInputFolderPath = "C:\Users\Guest\Documents\TestFolder\"
- 'Get the name of the first file in folder specified.
- sFileName = Dir(sInputFolderPath)
- 'sFileName = Dir(sInputFolderPath & "*.xlsx")
- 'Loop through all files in "Input Folder".
- Do While sFileName <> ""
- MsgBox sFileName
- 'Move to the next file in folder and get its name.
- sFileName = Dir()
- End Sub
a) Line 9 - Commented line indicates how to specify filters - in the commented code since "*.xlsx" is specified, search is made only for files of .xlsx type. This could be extended to other file types by using