List the Files in a Folder and SubFolders
The following procedure uses the FileSystemObject to list in the active worksheet the files in a folder and subfolders. 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 objFSO As
Scripting.FileSystemObject
Dim objTopFolder
As
Scripting.Folder
Dim
strTopFolderName As
String
'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"
'Assign the top folder to a
variable
strTopFolderName =
"C:\Users\Domenic\Documents"
'Create an instance of the
FileSystemObject
Set objFSO =
CreateObject("Scripting.FileSystemObject")
'Get the top folder
Set objTopFolder
= objFSO.GetFolder(strTopFolderName)
'Call the RecursiveFolder
routine
Call
RecursiveFolder(objTopFolder, True)
'Change the width of the
columns to achieve the best fit
Columns.AutoFit
End Sub
Sub
RecursiveFolder(objFolder As
Scripting.Folder, _
IncludeSubFolders As Boolean)
'Declare the variables
Dim objFile As Scripting.File
Dim objSubFolder
As
Scripting.Folder
Dim NextRow As Long
'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
Cells(NextRow,
"A").Value = objFile.Name
Cells(NextRow,
"B").Value = objFile.Size
Cells(NextRow,
"C").Value = objFile.Type
Cells(NextRow,
"D").Value = objFile.DateCreated
Cells(NextRow,
"E").Value = objFile.DateLastAccessed
Cells(NextRow,
"F").Value = objFile.DateLastModified
NextRow
= NextRow
+ 1
Next objFile
'Loop through files in the
subfolders
If
IncludeSubFolders Then
For Each
objSubFolder In
objFolder.SubFolders
Call
RecursiveFolder(objSubFolder, True)
Next
objSubFolder
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