List the Files in a Folder and its SubFolders using the FileSystemObject

The following procedure uses the FileSystemObject to list the files in a folder and its subfolders. The files will be listed in a worksheet in a newly created workbook.

Note that the FileSystemObject is a component of the Microsoft Scripting library, which requires Excel 2000 or later. Also, you'll need to set a reference to Microsoft Scripting Runtime by using Tools > References in the Visual Basic Editor (Alt+F11).

'Force the explicit delcaration of variables
Option Explicit

Sub ListFiles()

    'Set a reference to Microsoft Scripting Runtime by using
    'Tools > References in the Visual Basic Editor (Alt+F11)
    
    'Declare the variables
    Dim oFSO As Scripting.FileSystemObject
    Dim oFolder As Scripting.Folder
    Dim sPath As String
    Dim aFiles() As String
    Dim lFileCnt As Long
    Dim sErrMsg As String
    
    'Enable error handling
    On Error GoTo ErrHandler
    
    'Specify the path to the folder
    sPath = "C:\Users\Domenic\Documents"
    
    'Create an instance of the FileSystemObject
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    'Make sure the folder exists
    If Not oFSO.FolderExists(sPath) Then
        sErrMsg = "No such folder exists!"
        GoTo ErrHandler
    End If
    
    'Get the folder
    Set oFolder = oFSO.GetFolder(sPath)
    
    'Get the file names from the specified folder and its subfolders into an array
    Call RecursiveFolder(oFolder, aFiles, lFileCnt, True)
    
    'Transfer the list of files from the array to a worksheet in a new workbook
    If lFileCnt > 0 Then
        Workbooks.Add xlWBATWorksheet
        Range("A1").Value = "File Name"
        Range("B1").Value = "File Size (bytes)"
        Range("C1").Value = "File Type"
        Range("D1").Value = "Date Created"
        Range("E1").Value = "Date Last Accessed"
        Range("F1").Value = "Date Last Modified"
        Columns("B").NumberFormat = "#,##0"
        Columns("D:F").NumberFormat = "m/dd/yy h:mm AM/PM"
        Range("A2").Resize(UBound(aFiles, 2), UBound(aFiles, 1)).Value = Application.Transpose(aFiles)
        Columns("A:F").AutoFit
    Else
        MsgBox "No files found!", vbExclamation
    End If
    
ExitSub:
    Set oFSO = Nothing
    Set oFolder = Nothing
    Exit Sub
    
    'Error handling
ErrHandler:
    If Len(sErrMsg) > 0 Then
        MsgBox sErrMsg, vbExclamation
        GoTo ExitSub
    Else
        MsgBox "Error " & Err.Number & ":  " & Err.Description
        Resume ExitSub
    End If
    
End Sub

Sub RecursiveFolder(ByRef oFolder As Scripting.Folder, ByRef aFiles() As String, _
                    ByRef lFileCnt As Long, ByRef bIncludeSubFolders As Boolean)

    'Declare the variables
    Dim oFile As Scripting.File
    Dim oSubFolder As Scripting.Folder
    
    'Loop through each file in the folder
    For Each oFile In oFolder.Files
        lFileCnt = lFileCnt + 1
        ReDim Preserve aFiles(1 To 6, 1 To lFileCnt)
        aFiles(1, lFileCnt) = oFile.Name
        aFiles(2, lFileCnt) = oFile.Size
        aFiles(3, lFileCnt) = oFile.Type
        aFiles(4, lFileCnt) = oFile.DateCreated
        aFiles(5, lFileCnt) = oFile.DateLastAccessed
        aFiles(6, lFileCnt) = oFile.DateLastModified
    Next oFile
    
    'Loop through files in the subfolders
    If bIncludeSubFolders Then
        For Each oSubFolder In oFolder.SubFolders
            Call RecursiveFolder(oSubFolder, aFiles, lFileCnt, True)
        Next oSubFolder
    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 | Blog | About | Contact