In this article, I’ll demonstrate how to use Excel VBA to List Files in Folder. Basically, it will compile a list of all the file names in a folder. On many occasions, you may find it necessary to list out all the files in a folder. Manually copying each file name and then pasting it on Excel will be a very tedious process and if there are a lot of files, it can take hours if not days. This is where you can utilize the power of VBA to do this work in a matter of seconds and save a huge amount of time.
Some Occasions Where You Need to List Files in a Folder
Here are several scenarios in which you might need to use VBA to create a list of all the files in a folder:
- While doing a legal or regulatory compliance audit or inventory of files in a specific folder.
- While attempting to identify duplicate or updated or deleted files, particularly in large folders with multiple subdirectories.
- When planning to migrate or back up files from one location to another, having a detailed list of files will help ensure that all files are successfully transferred.
- When making a report or analysis of file types or sizes in a single folder or collection of folders, having a list of files and their attributes available can be used to determine metrics such as file size, frequency of use, or storage requirements.
- When looking for certain files or file patterns, such as files with a specific extension, naming convention, or content, having a list of files might help with targeted searches or data analysis.
Overall, making a list of files in a folder can be a useful tool for a range of administrative, analytical, and operational tasks, especially when there are a large number of files or the files are dispersed across numerous directories.
How to Launch VBA Editor in Excel
In this section, we are going to demonstrate how to launch VBA Editor & create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.
- First, we go to the Developer tab.
- Then we will select Visual Basic.
Then a new window will pop up. Then we are going to follow these steps,
- First, we will select Insert,
- Then we are going to select Module.
- A new Module will be created.
Excel VBA to List Files in Folder: 4 Suitable Methods
In this section, we will demonstrate 4 effective methods to list Files in a Folder in an Excel worksheet and the Immediate Window. In different methods, I have used different Objects and Functions. So, let’s explore those methods one by one. Before that, let’s look at the address of the folder on my PC from where the list of files will be generated.
1. Using FileSystemObject to Make a List of Files in a Folder in the Worksheet
In the first method, we will utilize the FileSystemObject of VBA that enables us to access the file system of the user’s PC for creating a list of files in the Microsoft Worksheet.
To create the 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 Sub🔎 How Does the Code Work?
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 WithThis prompts the File Explorer from where we need to select the folder whose file list we want to make. The address of that folder will be assigned as String to Selected_Folder. If the user selects multiple folders then 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)Here we created a new FileSystemObejct and assigned it to Ob_FSO. Then we use the .GetFolder method of Ob_FSO to assign 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_FileHere, the Ob_Folder.Files property is used to loop through each file in the Selected_Folder using For Each loop and the name of each file is written down on the active worksheet starting from B4.
Now, to make a list of files, follow the steps below.
Steps:
- Run the code using the F5 key or clicking on the Play button.
- As a result, a dialogue box will appear asking us to select the Folder whose file list we want to make. Here, select your desired Folder. I am selecting the Test Folder.
- As a result, a list of all the files will be created on the worksheet.
- Here, we can see that all the files are listed on the worksheet.
Read More: Excel VBA to Count Files in Folder and Subfolders
2. Using User-Defined Function to Make List of Files in a Folder in Worksheet
Now, we will create a user-defined function that will take the folder address as the argument and return all the file names in the folder. To do that, write the following code on 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 Function🔎 How Does the Code Work?
Set Ob_FSO = CreateObject("Scripting.FileSystemObject")
Set Ob_Folder = Ob_FSO.GetFolder(spath)
Set ob_Files = Ob_Folder.FilesHere we created a new FileSystemObejct and assigned it to Ob_FSO. Then we use the .GetFolder method of Ob_FSO to assign the spath to Ob_Folder. After that, we assigned all the files in that folder to the ob_Files object.
For Each Ob_File In ob_Files
va_Array(i) = Ob_File.Name
i = i + 1
NextHere, all the file names are stored in an array named va_Array using For Each loop.
listfiles = WorksheetFunction.Transpose(va_Array)The function listfiles returns the transposed array of va_array.
Now if we go to the worksheet and write down the following formula, we will be able to extract all the files in the E:\study\Office\Article 72 List Files\Test Folder.
Now, if we type enter, we will have all the file names.
Read More: Excel VBA to List Files in Folder and Subfolders
3. Using FileSystemObject to Make a List of Files in a Folder in Immediate Window
We can also display a list of files inside a folder on Immediate Window instead of showing them on a worksheet. To do that, 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 Sub🔎 How Does the Code Work?
The code works very similarly to the code in Method 1. The only difference is that here we are displaying the names of all the files inside a folder on Immediate Window using Debug.Print.
To open the immediate window and create a list of files, follow the steps below.
Steps:
- To open the immediate window, press Ctrl+G or go to View >> Immediate Window.
- As a result, you will see the immediate window.
- Now, run the code by pressing F5. then in the popped-up window, choose the folder whose files you want to list in the immediate window. I’m selecting Test Folder. Then click OK.
- As a result, you will see all the file names of the folder in the Immediate window.
4. Using Dir Function to Make List of Files in a Folder in Worksheet
In this method, we will use the VBA Dir function to make a list of files in a Folder and the output will be shown on the worksheet. The Dir function returns a string type value that represents a file name with specific patterns. Most importantly, the Dir function can take wildcard characters as the argument. The VBA code contains the Dir function for creating a file list of a folder.
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 Sub🔎 How Does the Code Work?
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 WithThis portion of the code opens the File dialogue and asks the user to select the folder whose file list needs to be made. 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 IfThis portion of the code add a backslash to the file address of the folder(Selected_Folder) if it is not already been added.
If sFilter = "" Then
sFilter = "*.*"
End IfHere, a string type variable sFilter is taken 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 taken and the return value of 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
LoopHere a Do Until loop is applied to extract all the file names in the Selected_Folder and their names are written on the worksheet starting from cell B4.
Now if we run the code by clicking F5, a new window will open and it will ask to select a folder for extracting file names.
After selecting the folder, we will get the list of files in the worksheet of that selected folder.
Read More: Excel VBA to List Files in Folder with Specific Extension
How to List All Files in a Folder with a Specific Extension with Excel VBA
In the previous section, we used the VBA Dir function to get all the file names in a folder. But sometimes we need to list out only a specific type of files within a folder. We can do that with a little modification of the code. On the line,
sFilter = "*.*"Replace the last * with your desired file extension such as xlsx, jpg, png, pdf, etc. For example, if we want to list out only the png file, we would replace the line with
sFilter = "*.png"Hence the complete code will be like this.
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 Sub🔎 How Does the Code Work?
To know how the code is working, see the explanation given on the 4th method.
Now if I run the code like the previous examples and select the Target folder, it will only list out the png files of the selected folder.
We can see that the GIF file is missing from the list as it only lists out the png files.
How to List All Files in a Folder & Its SubFolders with Excel VBA
Sometimes we may want to list out not only the files in a folder but also those which are inside subfolders of that folder in Excel Worksheet. For example, in a folder named Test Folder, there is a total of 6 Items, and in a subfolder named Folder1, there are another 5 Items.
To do that, we can 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 Function🔎 How Does the Code Work?
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 FunctionThis function prompts 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 SubThis subroutine writes the name of all the 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 SubThis is the main subroutine that calls another subroutine ListSubFolderFiles for extracting file names inside all the subfolders and the folder.
Now I ran the first subroutine ListFilesSubFolder and then selected the Test Folder. As a result, a list of all the files in the Test Folder including the subfolder was created.
How to List All Files in Folders & SubFolders Including File Details with Excel VBA
Sometimes we not only need to list the names of files in a folder and its subfolders but also the all the details such as Path, folder, File Extension, Data Created, Last Accessed, Last Modified, Size, and Hidden Status. In those situations, you can use the following code to extract all those information with a single click.
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
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 Function🔎 How Does the Code Work?
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 FunctionThis function creates a new sheet. Then it writes down the titles of the columns which will contain different properties of the files such as Path, Folder, File Name, etc.
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 FunctionThis function prompts 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 SubThis is the main subroutine which will call the previous two functions for selecting a single folder and creating a new worksheet with a custom name. Then it will extract every information of each file and write it down in the newly created sheet.
Now if we run this code, a window will open and ask to select a folder. After selecting the folder, a new worksheet will be created named Files and all the details of the files will be listed there.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
That is the end of this article regarding how to use Excel VBA to List Files in Folder. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.
