Excel VBA: List All the Excel Files in a folder

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.

The Code

Sub ImportFileList()

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

With Application.FileDialog(msoFileDialogFolderPicker)

    .Title = "Please select a folder"

    .Show

    .AllowMultiSelect = False

    If .SelectedItems.Count = 0 Then

        MsgBox "You did not select a folder"

        Exit Sub

    End If

    MyFolder = .SelectedItems(1) & "\"

End With

'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

Loop

Application.ScreenUpdating = True

End Sub

See Also

• Loop through all the files in a folder using Excel VBA
• Use the OnTime method in Excel VBA