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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Sub ListFiles() Range("A:A").ClearContents Range("A1").Select 'All Files '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 ActiveCell.Offset(1, 0).Select F = Dir() Loop End Sub |
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!
Additional Content
Check out more examples by visiting my Home Page
Here you will find topics covering Qlikview SQL Server Excel VBA
This came in handy. Can this macro be expanded to show all the sub-folders and files within those sub-folders. Thanks.
I just setup a new post that will list all files in a given path, including any that are embedded in any folders and subfolders.
Here is the link:
Excel VBA List Files in Folders and Subfolders