The following macro will loop through the files in specified folder and list them out in your Excel worksheet, based on a designated file type (i.e. anything with file extension of “XLSX”).
The actual VBA code is quite simple and can be copied into your VBA Project (modify to suit your needs). As always, I encourage you to copy down the code and give it a try for yourself.
Excel VBA – List Files in Folder – Setup
'F = Dir("C:\File Destination\")
'Only XLS Files
'F = Dir("C:\File Destination\*XLS")
'Only XLSX Files
F = Dir("C:\File Destination\*XLSX")
Do While Len(F) > 0
ActiveCell.Formula = F
F = Dir()
For simplicity, say you have the following files in a given folder:
Run the macro “ListFiles”, and you will see the following results. A simple list of each file name in the specified folder you want to loop through:
Now, comment/uncomment the macro above to only list files that have a file extension beginning with “XLS”, and you will get the following results:
Now, comment/uncomment the macro above to only list files that have a file extension beginning with “XLSX”, and you will get the following results:
You get the idea. This comes in handy when you are trying to identify specific files in a given folder to perform additional actions. I use this when I need to make updates to a list of files through VBA code, and I list them out first so I can record data or perform specific updates on a file by file basis.
You can modify this macro to meet your needs. If you have any comments or questions, please send me your thoughts!