Macro to List Files and Subfolders

These VBA macros list every file in a folder on a worksheet, with the size, date and a clickable link, and a second version includes all subfolders too.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

List the files in a folder

Run it on an empty sheet and pick the folder:

Sub ListFiles()
    Dim p As String, f As String, r As Long
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = 0 Then Exit Sub
        p = .SelectedItems(1) & Application.PathSeparator
    End With
    Range("A1:C1").Value = Array("File name", "Size (KB)", "Modified")
    r = 1
    f = Dir(p & "*.*")
    Do While f <> ""
        r = r + 1
        ActiveSheet.Hyperlinks.Add Cells(r, 1), p & f, TextToDisplay:=f
        Cells(r, 2).Value = Round(FileLen(p & f) / 1024, 1)
        Cells(r, 3).Value = FileDateTime(p & f)
        f = Dir()
    Loop
    Columns("A:C").AutoFit
End Sub

Each file name is a link, so you can open the file straight from the list. Change "*.*" to "*.pdf" to list only PDFs.

Include all subfolders

Sub ListFilesWithSubfolders()
    Dim p As String, r As Long
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = 0 Then Exit Sub
        p = .SelectedItems(1)
    End With
    Range("A1:C1").Value = Array("Folder", "File name", "Size (KB)")
    r = 1
    ListFolder CreateObject("Scripting.FileSystemObject").GetFolder(p), r
    Columns("A:C").AutoFit
End Sub

Sub ListFolder(fld As Object, r As Long)
    Dim f As Object, sf As Object
    For Each f In fld.Files
        r = r + 1
        Cells(r, 1).Value = fld.Path
        Cells(r, 2).Value = f.Name
        Cells(r, 3).Value = Round(f.Size / 1024, 1)
    Next f
    For Each sf In fld.SubFolders
        ListFolder sf, r
    Next sf
End Sub

ListFolder calls itself for each subfolder, so it goes as deep as the folders go. Paste both parts into the same module.

Without a macro (Excel 365)

Power Query lists files too: Data → Get Data → From File → From Folder, pick the folder, then Transform Data. It includes subfolders, and you can refresh the list later.

Things to know

  • The subfolder version uses Windows’ FileSystemObject, so it works on Windows only. The first macro works on both.
  • Large folders with thousands of files take a few seconds.
  • Clear the sheet before running again, or the new list writes over the old one.

Related macros