List the Files in a Folder and SubFolders

For Excel 2000 and later versions, the following macro lists in the active worksheet the files in a folder and subfolders. Note that you'll need to set up a reference to the Windows Script Host Object Module by using Tools > References in the Visual Basic Editor.

'Force the explicit delcaration of variables
Option Explicit

Sub ListFiles()

    'Set up a reference to Windows Script Host Object Model by
    'using Tools > References in the VBE
    
    'Declare the variable
    Dim objFSO As FileSystemObject
    
    'Insert the headers for Columns A through F
    Range("A1").Value = "File Name"
    Range("B1").Value = "File Size"
    Range("C1").Value = "File Type"
    Range("D1").Value = "Date Created"
    Range("E1").Value = "Date Last Accessed"
    Range("F1").Value = "Date Last Modified"
    
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Call the RecursiveFolder routine
    Call RecursiveFolder(objFSO, "C:\Users\Domenic\Documents", True)
    
    'Change the width of the columns to achieve the best fit
    Columns.AutoFit
    
End Sub

Sub RecursiveFolder( _
    FSO As FileSystemObject, _
    MyPath As String, _
    IncludeSubFolders As Boolean)

    'Declare the variables
    Dim File As File
    Dim Folder As Folder
    Dim SubFolder As Folder
    Dim NextRow As Long
    
    'Find the next available row
    NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    'Set the folder
    Set Folder = FSO.GetFolder(MyPath)
    
    'Loop through each file in the folder
    For Each File In Folder.Files
        Cells(NextRow, "A").Value = File.Name
        Cells(NextRow, "B").Value = File.Size
        Cells(NextRow, "C").Value = File.Type
        Cells(NextRow, "D").Value = File.DateCreated
        Cells(NextRow, "E").Value = File.DateLastAccessed
        Cells(NextRow, "F").Value = File.DateLastModified
        NextRow = NextRow + 1
    Next File
    
    'Loop through files in the subfolders
    If IncludeSubFolders Then
        For Each SubFolder In Folder.SubFolders
            Call RecursiveFolder(FSO, SubFolder.Path, True)
        Next SubFolder
    End If
    
End Sub

Where to Put the Code

  1. Open the workbook in which to store the code.
  2. Open the Visual Basic Editor (Alt+F11).
  3. Insert a standard module (Insert > Module).
  4. Copy/paste the above code into the module.
  5. Return to Microsoft Excel (Alt+Q).
  6. Save the workbook.

How to Use the Macro

  1. Display the Macro dialog box (Alt+F8).
  2. Click/select the macro called "ListFiles".
  3. Click/select "Run".

Sample Workbook: Download

Home | Excel | VBA | About | Contact