This is an overview.
How to Launch VBA Editor in Excel
- Go to the Developer tab on the ribbon or enable the Developer tab on your ribbon..
- Select Visual Basic.
In the new window:
- Select Insert,
- Choose Module.
- A new Module will be created.
The list of files will be generated in this folder.
Method 1 – Using FileSystemObject to create a List of Files in a Folder
This is the sample dataset.
- To create a list of files, run the following VBA code.
Code Syntax:
'1.Using FileSystemObject
Sub ListFiles_1()
Dim Ob_FSO As Object
Dim Ob_Folder As Object
Dim Ob_File As Object
Dim i As Integer
Dim Selected_Folder As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a folder for creating list of files"
.Show
If .SelectedItems.Count > 0 Then
Selected_Folder = .SelectedItems(1)
Else
End If
End With
Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
Set Ob_Folder = Ob_FSO.GetFolder(Selected_Folder)
For Each Ob_File In Ob_Folder.Files
Cells(i + 4, 2) = Ob_File.Name
i = i + 1
Next Ob_File
End SubCode Breakdowm
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a folder for creating list of files"
.Show
If .SelectedItems.Count > 0 Then
Selected_Folder = .SelectedItems(1)
Else
End If
End WithThe File Explorer selects the folder. The address of that folder is assigned as String to Selected_Folder. If the user selects multiple folders, only the first selection will be taken using the If statement.
Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
Set Ob_Folder = Ob_FSO.GetFolder(Selected_Folder)A new FileSystemObejct was created and assigned to Ob_FSO. The .GetFolder method of Ob_FSO assigns the Selected_Folder to Ob_Folder.
For Each Ob_File In Ob_Folder.Files
Cells(i + 4, 2) = Ob_File.Name
i = i + 1
Next Ob_FileThe Ob_Folder.Files property is used to loop through each file in the Selected_Folder using For Each loop and enter the name of each file in the active worksheet starting from B4.
Steps:
- Run the code by pressing F5 key or clicking the Play button.
- In the dialog box, select a Folder (Test, here).
A list of all the files will be created.
Read More: Excel VBA to Count Files in Folder and Subfolders
Method 2 – Using the User-Defined Function to create a List of Files in a Folder
- Enter the following code in the VBA code window.
Code Syntax:
Function listfiles(ByVal spath As String)
Dim va_Array As Variant
Dim i As Integer
Dim Ob_File As Object
Dim Ob_FSO As Object
Dim Ob_Folder As Object
Dim ob_Files As Object
Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
Set Ob_Folder = Ob_FSO.GetFolder(spath)
Set ob_Files = Ob_Folder.Files
If ob_Files.Count = 0 Then Exit Function
ReDim va_Array(1 To ob_Files.Count)
i = 1
For Each Ob_File In ob_Files
va_Array(i) = Ob_File.Name
i = i + 1
Next
listfiles = WorksheetFunction.Transpose(va_Array)
End FunctionCode Breakdown
Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
Set Ob_Folder = Ob_FSO.GetFolder(spath)
Set ob_Files = Ob_Folder.FilesA new FileSystemObejct was created and assigned to Ob_FSO. The .GetFolder method of Ob_FSO assigns the spath to Ob_Folder. All files in that folder are assigned to the ob_Files object.
For Each Ob_File In ob_Files
va_Array(i) = Ob_File.Name
i = i + 1
NextAll file names are stored in an array (va_Array), using the For Each loop.
listfiles = WorksheetFunction.Transpose(va_Array)The function listfiles returns the transposed array of va_array.
- Enter the following formula to extract all the files in E:\study\Office\Article 72 List Files\Test Folder.
- Press enter to see the output.
Read More: Excel VBA to List Files in Folder and Subfolders
Method 3 – Using FileSystemObject to create a List of Files in a Folder in an Immediate Window
- Use the following code.
Code Syntax:
' 3.Using FileSystmeObject
'Printing in Immediate Window
Public Sub ListFiles_ImWin()
Dim spath As String
Dim i As Integer
Dim Selected_Folder As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a folder"
.Show
If .SelectedItems.Count > 0 Then
Selected_Folder = .SelectedItems(1)
Else
End If
End With
Dim Ob_FSO As Object
Dim Ob_Folder As Object
Dim Ob_File As Object
Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
Set Ob_Folder = Ob_FSO.GetFolder(Selected_Folder)
For Each Ob_File In Ob_Folder.Files
Debug.Print Ob_File.Name
Next Ob_File
Set Ob_File = Nothing
Set Ob_Folder = Nothing
Set Ob_FSO = Nothing
End SubCode Breakdown
The code is very similar to the one used in Method 1. The only difference is that here we are displaying the names of all files inside a folder in an Immediate Window, using Debug.Print.
Steps:
- Press Ctrl+G or go to View >> Immediate Window.
The immediate window will be displayed.
- Run the code by pressing F5. In the new window, choose the folder whose files you want to list in the immediate window.Test Folder, here.
- Click OK.
All file names in the folder will be displayed in the Immediate window.
Method 4 – Using Dir Function to create List of Files in a Folder
- Use the code:
Code Syntax:
'4. Using Dir Function
'Printing in Immediate window
Public Sub ListFilesDir()
Dim Selected_Folder As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a folder"
.Show
If .SelectedItems.Count > 0 Then
Selected_Folder = .SelectedItems(1)
Else
End If
End With
Dim sFile As String
If Right(Selected_Folder, 1) <> "\" Then
Selected_Folder = Selected_Folder & "\"
End If
If sFilter = "" Then
sFilter = "*.*"
End If
'call with path "initializes" the dir function and returns the first file name
sFile = Dir(Selected_Folder & sFilter)
i = 4 'Start Writing file names from 4th row
'call it again until there are no more files
Do Until sFile = ""
Cells(i, 2) = sFile
i = i + 1
'subsequent calls without parameter return next file name
sFile = Dir
Loop
End SubCode Breakdown
Dim Selected_Folder As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a folder"
.Show
If .SelectedItems.Count > 0 Then
Selected_Folder = .SelectedItems(1)
Else
End If
End Withopens the File dialog and asks the user to select the folder. The selected folder’s address is stored in a string-type variable named Selected_Folder.
If Right(Selected_Folder, 1) <> "\" Then
Selected_Folder = Selected_Folder & "\"
End Ifadds a backslash to the file address of the folder(Selected_Folder).
If sFilter = "" Then
sFilter = "*.*"
End Ifa string type variable sFilter is used and a string with wildcards containing filename pattern(“*.*”)is assigned.
sFile = Dir(Selected_Folder & sFilter)Dir(Selected_Folder & sFilter) returns the first file name of the Selected_Folder
A new string type variable sFile is used and the return value of the Dir function is assigned to the sFile variable.
Do Until sFile = ""
Cells(i, 2) = sFile
i = i + 1
'subsequent calls without parameter return next file name
sFile = Dir
Loopa Do Until loop is applied to extract all file names in the Selected_Folder and enter them in the worksheet starting from B4.
- Run the code by clicking F5.
- In the new window, select a folder to extract the file names.
The list of files in the worksheet will be displayed in the selected folder.
How to List All Files in a Folder with a Specific Extension with Excel VBA
- Change this code line:
sFilter = "*.*"Replace the last * with a file extension such as xlsx, jpg, png, pdf, etc.
sFilter = "*.png"This will be the new code.
Code Syntax:
Public Sub List_SpecificFiles()
Dim Selected_Folder As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a folder"
.Show
If .SelectedItems.Count > 0 Then
Selected_Folder = .SelectedItems(1)
Else
End If
End With
Dim sFile As String
If Right(Selected_Folder, 1) <> "\" Then
Selected_Folder = Selected_Folder & "\"
End If
If sFilter = "" Then
sFilter = "*.png" ' type your desired file extension here
End If
sFile = Dir(Selected_Folder & sFilter)
i = 4
Do Until sFile = ""
Cells(i, 2) = sFile
i = i + 1
sFile = Dir
Loop
End SubCode Breakdown
Check Code Breakdown in Method 4.
- Run the code and select the Target folder.
Only png files are listed.
Read More: Excel VBA to List Files in Folder with Specific Extension
How to List All Files in a Folder & Its SubFolders with Excel VBA
In the example below there is the Test Folder with a total of 6 Items, and a subfolder (Folder1) with 5 Items.
- Use the following VBA Code.
Code Syntax
Sub ListFilesSubFolder()
Dim Obj_FSO As Object
Dim Obj_fld As Object
Dim sf As Object
Dim file As Object
Dim folderPath As String
Dim row As Long
Set Obj_FSO = CreateObject("Scripting.FileSystemObject")
Set Obj_fld = Obj_FSO.getFolder(GetFolderPath)
folderPath = Obj_fld.Path
row = 4
For Each file In Obj_FSO.getFolder(folderPath).Files
Cells(row, 2) = file.Name
row = row + 1
Next file
For Each sf In Obj_FSO.getFolder(folderPath).SubFolders
ListSubFolderFiles sf, row
Next sf
End Sub
Sub ListSubFolderFiles(ByRef subFolder As Object, ByRef row As Long)
Dim Obj_FSO As Object
Dim file As Object
Set Obj_FSO = CreateObject("Scripting.FileSystemObject")
For Each file In subFolder.Files
Cells(row, 2) = file.Name
row = row + 1
Next file
For Each subFolder In subFolder.SubFolders
ListSubFolderFiles subFolder, row
Next subFolder
End Sub
Function GetFolderPath() As String
Dim fldr As FileDialog
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Function
GetFolderPath = .SelectedItems(1)
End With
End FunctionCode Breakdown
Function GetFolderPath() As String
Dim fldr As FileDialog
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Function
GetFolderPath = .SelectedItems(1)
End With
End Functionprompts a window to select a folder.
Sub ListSubFolderFiles(ByRef subFolder As Object, ByRef row As Long)
Dim Obj_FSO As Object
Dim file As Object
Set Obj_FSO = CreateObject("Scripting.FileSystemObject")
For Each file In subFolder.Files
Cells(row, 2) = file.Name
row = row + 1
Next file
For Each subFolder In subFolder.SubFolders
ListSubFolderFiles subFolder, row
Next subFolder
End Subenters the name of all files in the folder and subfolder in the worksheet.
Sub ListFilesSubFolder()
Dim Obj_FSO As Object
Dim Obj_fld As Object
Dim sf As Object
Dim file As Object
Dim folderPath As String
Dim row As Long
Set Obj_FSO = CreateObject("Scripting.FileSystemObject")
Set Obj_fld = Obj_FSO.getFolder(GetFolderPath)
folderPath = Obj_fld.Path
row = 4
For Each file In Obj_FSO.getFolder(folderPath).Files
Cells(row, 2) = file.Name
row = row + 1
Next file
For Each sf In Obj_FSO.getFolder(folderPath).SubFolders
ListSubFolderFiles sf, row
Next sf
End Subcalls another subroutine ListSubFolderFiles to extract file names inside all subfolders and the folder.
the first subroutine ListFilesSubFolder was run and the Test Folder was selected.
This is the output.
How to List All Files in Folders & SubFolders Including File Details with Excel VBA
- Use the following code.
Code Syntax:
‘List All Files in Folders & SubFolders Including File Details
Option Explicit
Sub ListFilewithDetails()
Dim Path_Spec As String
Path_Spec = "" 'Specify a folder
If (Path_Spec = "") Then Path_Spec = SelectSingleFolder 'Browse for Folder to select a folder
Dim Ob_FSO As Object
Set Ob_FSO = CreateObject("Scripting.FileSystemObject") 'Late Binding
If (Ob_FSO.FolderExists(Path_Spec) = False) Then Exit Sub 'folder exist or not?
Application.ScreenUpdating = False 'Disable Screen Updating to speed up macro
Dim My_Sheet_Name As String
My_Sheet_Name = "Files" 'Add a Sheet with name "Files"
Add_Sheet (My_Sheet_Name)
Dim File_Type As String
File_Type = "*" '*:all, or pdf, PDF, XLSX...
File_Type = UCase(File_Type)
Dim Coll_queue As Collection, oFolder As Object, oSubfolder As Object, oFile As Object
Dim LastBlankCell As Long, FileExtension As String
Set Coll_queue = New Collection
Coll_queue.Add Ob_FSO.getFolder(Path_Spec) 'enqueue
Do While Coll_queue.Count > 0
Set oFolder = Coll_queue(1) 'Assign folder before removing it
Coll_queue.Remove 1 'dequeue
For Each oSubfolder In oFolder.SubFolders 'loop all sub-folders
Coll_queue.Add oSubfolder 'enqueue
'...insert any folder processing code here...
Next oSubfolder
LastBlankCell = ThisWorkbook.Sheets(My_Sheet_Name).Cells(Rows.Count, 1).End(xlUp).row + 1 'get the last blank cell of column A
For Each oFile In oFolder.Files 'loop all files
FileExtension = UCase(Split(oFile.Name, ".")(UBound(Split(oFile.Name, ".")))) 'get file extension, eg: TXT
If (File_Type = "*" Or FileExtension = File_Type) Then
With ThisWorkbook.Sheets(My_Sheet_Name)
.Cells(LastBlankCell, 1) = oFile 'Path
.Cells(LastBlankCell, 2) = oFolder 'Folder
.Cells(LastBlankCell, 3) = oFile.Name 'File Name
.Cells(LastBlankCell, 4) = FileExtension 'File Extension
.Cells(LastBlankCell, 5) = oFile.DateCreated 'Data Created
.Cells(LastBlankCell, 6) = oFile.DateLastAccessed 'Last Accessed
.Cells(LastBlankCell, 7) = oFile.DateLastModified 'Last Modified
.Cells(LastBlankCell, 8) = oFile.Size 'File Size
If (oFile.Attributes And 2) = 2 Then
.Cells(LastBlankCell, 9) = "TRUE" 'Is Hidden
Else
.Cells(LastBlankCell, 9) = "FALSE" 'Is Hidden
End If
End With
LastBlankCell = LastBlankCell + 1
End If
Next oFile
Loop
'Cells.EntireColumn.AutoFit 'Autofit columns width
Application.ScreenUpdating = True
End Sub
Function SelectSingleFolder()
'Select a Folder Path
Dim FolderPicker As FileDialog
Dim myFolder As String
'Select Folder with Dialog Box
Set FolderPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FolderPicker
.Title = "Select A Single Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Function 'Check if user clicked cancel button
SelectSingleFolder = .SelectedItems(1)
End With
End Function
Function Add_Sheet(My_Sheet_Name As String)
'Add a worksheet with custom name
Dim Mysheet As Worksheet, F As Boolean
For Each Mysheet In ThisWorkbook.Worksheets
If Mysheet.Name = My_Sheet_Name Then
Sheets(My_Sheet_Name).Cells.Delete
F = True
Exit For
Else
F = False
End If
Next
If Not F Then Sheets.Add.Name = My_Sheet_Name
'Add table header
With Sheets(My_Sheet_Name)
.Cells(1, 1) = "Path"
.Cells(1, 2) = "Folder"
.Cells(1, 3) = "File Name"
.Cells(1, 4) = "File Extension"
.Cells(1, 5) = "Data Created"
.Cells(1, 6) = "Last Accessed"
.Cells(1, 7) = "Last Modified"
.Cells(1, 8) = "Size"
.Cells(1, 9) = "Is Hidden"
End With
End FunctionCode Breakdown
Function Add_Sheet(My_Sheet_Name As String)
'Add a worksheet with custom name
Dim Mysheet As Worksheet, F As Boolean
For Each Mysheet In ThisWorkbook.Worksheets
If Mysheet.Name = My_Sheet_Name Then
Sheets(My_Sheet_Name).Cells.Delete
F = True
Exit For
Else
F = False
End If
Next
If Not F Then Sheets.Add.Name = My_Sheet_Name
'Add table header
With Sheets(My_Sheet_Name)
.Cells(1, 1) = "Path"
.Cells(1, 2) = "Folder"
.Cells(1, 3) = "File Name"
.Cells(1, 4) = "File Extension"
.Cells(1, 5) = "Data Created"
.Cells(1, 6) = "Last Accessed"
.Cells(1, 7) = "Last Modified"
.Cells(1, 8) = "Size"
.Cells(1, 9) = "Is Hidden"
End With
End Functioncreates a new sheet; enter the titles of the columns and the properties of the files.
Function SelectSingleFolder()
'Select a Folder Path
Dim FolderPicker As FileDialog
Dim myFolder As String
'Select Folder with Dialog Box
Set FolderPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FolderPicker
.Title = "Select A Single Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Function 'Check if user clicked cancel button
SelectSingleFolder = .SelectedItems(1)
End With
End Functionprompts the user to select a folder.
Sub ListFilewithDetails()
Dim Path_Spec As String
Path_Spec = "" 'Specify a folder
If (Path_Spec = "") Then Path_Spec = SelectSingleFolder 'Browse for Folder to select a folder
My_Sheet_Name = "Files" 'Add a Sheet with name "Files"
Add_Sheet (My_Sheet_Name)
'Cells.EntireColumn.AutoFit 'Autofit columns width
Application.ScreenUpdating = True
End Subcalls the previous two functions to select a single folder and create a new worksheet with a custom name. It extracts information of each file and enters it in the new sheet.
- Run this code.
- In the new window, select a folder.
A new worksheet (Files) will be created and all details will be listed.
Download Practice Workbook
Download this practice workbook to exercise.
Related Articles
- Excel VBA to Loop Through Files in Folder and Rename
- How to Use Excel VBA to Move Files
- Excel VBA: Delete Files with Wildcards
I was very interested in the macro that listed the file details ListFileWithDetails, however, when I tried to execute it from the downloaded workbook, it errored out. oSubfolder was not recognized. Could you please help?
Hello Laurie,
Here, oFolder object not being correctly initialized within the Do While Coll_queue.Count > 0 loop. That’s why the error is occurring. This object is necessary to iterate through files and subfolders, but it’s missing an assignment before looping over subfolders and files.
To fix this issue, assigning oFolder from the collection at the beginning of the loop.
This ensures oFolder is properly initialized and recognized when iterating over its subfolders and files.
Updated Excel File:
List All Files in Folders & SubFolders Including File Details with Excel VBA.xlsm
Regards
ExcelDemy