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:

  1. Option Explicit
  2. Sub ListAllFilesInFolder()
  3. Dim sInputFolderPath As String, sFileName As String
  4. ''
  5. sInputFolderPath = "C:\Users\Guest\Documents\TestFolder\"
  6. ''
  7. 'Get the name of the first file in folder specified.
  8. sFileName = Dir(sInputFolderPath)
  9. 'sFileName = Dir(sInputFolderPath & "*.xlsx")
  10. ''
  11. 'Loop through all files in "Input Folder".
  12. Do While sFileName <> ""
  13. MsgBox sFileName
  14. ''
  15. 'Move to the next file in folder and get its name.
  16. sFileName = Dir()
  17. Loop
  18. End Sub

Description:

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

appropriate filters.

 

You can find similar Excel Questions and Answer hereunder

1) How can I copy and rename a WorkSheet using VBA?

2) How can I set up a drop down list?

3) How can I set the Source Data of charts using VBA?

4) How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

5) How can I create dependent drop down lists?

6) How do I assign a macro to a shape using VBA?

7) Userform initialize vs userform show in Excel

8) Line break in vba message box in Excel

9) How can I get input from user through a prompt and assign user's input to a cell?

10) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

11) How can I ensure that important files always appear on the recent Excel workbooks list?

12) How can I export a chart as a gif file?

13) How can I dynamically add a hyperlink using VBA?

14) How can I dynamically add series to an existing chart using VBA?

15) How do I update my DropDown list whenever the sheet is activated?