List the Files in a Folder with the DIR Function

The following procedure lists in the active worksheet the files in a folder with the DIR function...

'Force the explicit delcaration of variables
Option Explicit

Sub ListFiles()

    'Declare the variables
    Dim strPath As String
    Dim strFile As String
    Dim NextRow As Long
    'Specify the path to the folder
    strPath = "C:\Users\Domenic\Documents\"
    'Make sure that the path ends in backslash
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    'Get the first file from the folder
    strFile = Dir(strPath & "*.*", vbNormal)
    'If no files were found, exit the sub
    If Len(strFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    'Turn off screen updating
    Application.ScreenUpdating = False
    'Insert the headers for Columns A, B, and C
    Cells(1, "A").Value = "FileName"
    Cells(1, "B").Value = "Size"
    Cells(1, "C").Value = "Date/Time"
    'Find the next available row
    NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

    'Loop through each file in the folder
    Do While Len(strFile) > 0
        'List the name, size, and date/time of the current file
        Cells(NextRow, 1).Value = strFile
        Cells(NextRow, 2).Value = FileLen(strPath & strFile)
        Cells(NextRow, 3).Value = FileDateTime(strPath & strFile)
        'Determine the next row
        NextRow = NextRow + 1
        'Get the next file from the folder
        strFile = Dir
    'Change the width of the columns to achieve the best fit
    'Turn screen updating back on
    Application.ScreenUpdating = True
End Sub

Tip: To list only Excel files, replace...

strFile = Dir(strPath & "*.*", vbNormal)


strFile = Dir(strPath & "*.xls", vbNormal)

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