Using Excel VBA you can get a list of all the files in a folder and enter them on a worksheet. We can also retrieve certain information about the file if required. This macro will list the file names and the date they were last modified.
How does it Work?
The folder picker dialog box is used to make it easier for the user to select the folder they want to return files from. An If statement has been included to ensure that the user did select a folder.
The Dir function will be used to return each filename from the folder or directory. This function will then be used to iterate through each file in turn (Learn more about the Dir function in Excel VBA).
The macro in this tutorial will list all Excel files in a folder to the worksheet. This has been specified by using *.xls in the Dir function. The wildcard and extension can be altered to list the files you want, or omitted entirely to list all files in the folder.
The FileDateTime method has been used to capture the date the files were last created or modified.