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
Loop
'Change the width of the
columns to achieve the best fit
Columns.AutoFit
'Turn screen updating back on
Application.ScreenUpdating = True
End Sub
Tip:
To list only Excel files, replace...
strFile =
Dir(strPath & "*.*",
vbNormal)
with
strFile =
Dir(strPath & "*.xls",
vbNormal)
Where to Put the Code
- Open the workbook in which to store the code.
- Open the Visual Basic Editor (Alt+F11).
- Insert a standard module (Insert > Module).
- Copy/paste the above code into the module.
- Return to Microsoft Excel (Alt+Q).
- Save the workbook.
How to Use the Macro
- Display the Macro dialog box (Alt+F8).
- Click/select the macro called "ListFiles".
- Click/select "Run".
Sample Workbook: Download