List the Files in a Folder with the FileSystemObject
For Excel 2000 and later versions, the following macro lists in the active worksheet the files in a folder. 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
declaration of variables
Option Explicit
Sub
ListFiles()
'Set a reference to the
Windows Script Host Object Module by
'using Tools >
References in the VBE
'Declare the variables
Dim objFSO As
FileSystemObject
Dim objFolder As Folder
Dim objFile As File
Dim strPath As String
Dim strFile As String
Dim NextRow As Long
'Specify the path to the
folder
strPath =
"C:\Users\Domenic\Documents\"
'Create an instance of the
FileSystemObject
Set objFSO =
CreateObject("Scripting.FileSystemObject")
'Get the folder
Set objFolder =
objFSO.GetFolder(strPath)
If
objFolder.Files.Count > 0 Then
'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
For Each objFile In
objFolder.Files
'List the name, size, and
date/time of the current file
Cells(NextRow,
1).Value = objFile.Name
Cells(NextRow,
2).Value = objFile.Size
Cells(NextRow,
3).Value = objFile.DateLastModified
'Determine the next row
NextRow
= NextRow
+ 1
Next
objFile
'Change the width of the
columns to achieve the best fit
Columns.AutoFit
Else
MsgBox
"No files were found...", vbExclamation
End If
End Sub
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
Last Update: May 12, 2012