Monday, 5 August 2013

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"
    .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

Application.ScreenUpdating = True

End Sub

See Also