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.
Dim MyFolder As String 'Store the folder selected by the using
Dim FiletoList As String 'store the name of the file ready for listing
Dim NextRow As Long 'Store the row to write the filename to
On Error Resume Next
Application.ScreenUpdating = False
'Display the folder picker dialog box for user selection of directory
.Title = "Please select a folder"
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then
MsgBox "You did not select a folder"
MyFolder = .SelectedItems(1) & "\"
'Dir finds the first Excel workbook in the folder
FiletoList = Dir(MyFolder & "*.xls")
Range("A1").Value = "Filename"
Range("B1").Value = "Date Last Modified"
Range("A1:B1").Font.Bold = True
'Find the next empty row in the list
NextRow = Application.CountA(Range("A:A")) + 1
'Do whilst the dir function returns an Excel workbook
Do While FiletoList <> ""
Cells(NextRow, 1).Value = FiletoList 'Write the filename into the next available cell
Cells(NextRow, 2).Value = FileDateTime(MyFolder & FiletoList) 'Write the date the cell was last modified
NextRow = NextRow + 1 'Move to next row
FiletoList = Dir 'Dir returns the next Excel workbook in the folder
Application.ScreenUpdating = True