Managing large amounts of data in Excel can be very challenging, especially when it comes to locating and organizing files scattered across multiple folders and subfolders. Fortunately, VBA provides several built-in methods for listing files in a folder and its subfolders, making it easier to extract and manipulate data as needed. In this article, we will explore 3 different methods for making a list of files in folder and subfolders using VBA in Excel.
The methods include both recursive and non-recursive procedures. The methods covered in this article include the use of the Dir function, FSO early binding, and FSO late binding. By the end of this article, you’ll have a solid understanding of how to use VBA to efficiently manage your files and folders in Excel.
Key Concept of Function & Feature for Listing
i. Dir Function
The Dir function is a built-in function in Excel VBA. Its usage is straightforward and it does not necessitate any unique steps to activate it in the Visual Basic Editor. It returns the first file name in the specified folder.
ii. File System Object (FSO)
A File System Object (FSO) is an object in VBA that provides a way to work with files and folders on a computer’s file system. It allows VBA code to create, read, modify, and delete files and folders, as well as perform other file system operations, such as copying, moving, and renaming files and folders.
Early Binding
Early binding in VBA refers to explicitly declaring the type of an object at compile time, using the Dim statement with the As keyword. It is automatically opened when the workbook is launched. For this, we need to make sure that the Microsoft Scripting Runtime box is checked.
To do this, simply go to Tools and click on References.
Search Microsoft Scripting Runtime and check the box beside it.
Late Binding
Late binding in VBA refers to declaring an object without specifying its type until runtime. When using late binding with the File System Object (FSO), the CreateObject function is used to create an FSO object, rather than explicitly declaring it with the Dim statement.
How to Launch VBA Editor in Excel
To access the Microsoft Visual Basic window, go to the Developer tab and click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.
Then go to the Insert tab and click on Module to open the code Module.
Excel VBA to List Files in Folder and Subfolders: 3 Examples
In this article, we will demonstrate three easy ways to list files in folder and subfolders using recursive and non-recursive methods. We will list the files from the folder “E:\Softeko\UDF\”.
1. List All Files in a Folder in Excel Worksheet
In the first method, we will list files from the main folder only. We will do this by using the Dir function and the File System Object.
1.1 Use File System Object Early Binding
We will use the FSO Early Binding to list all the files of a folder in an Excel worksheet. We will write the following VBA code in the code editor Module for this purpose.
Sub ListFilesEarly()
Dim folderPath As String
Dim fileSystem As FileSystemObject
Dim folder As Object
Dim file As Object
Dim currentRow As Long
' Set the folder path to a variable
folderPath = "E:\Softeko\UDF\"
Set fileSystem = New FileSystemObject
Set folder = fileSystem.GetFolder(folderPath)
currentRow = 5
' Insert headers for the columns
Range("B4").Value = "File Name"
Range("C4").Value = "Type"
Range("D4").Value = "Size"
For Each file In folder.Files
Range("B" & currentRow).Value = file.Name
Range("C" & currentRow).Value = file.Type
Range("D" & currentRow).Value = file.Size
' Increment the current row
currentRow = currentRow + 1
Next
' Release the memory
Set fileSystem = Nothing
Set folder = Nothing
End Sub
Code Breakdown
folderPath = "E:\Softeko\UDF\"
Set fileSystem = New FileSystemObject
Set folder = fileSystem.GetFolder(folderPath)- folderPath = “E:\Softeko\UDF sets the value of the “folderPath” variable to the file path “E:\Softeko\UDF”.
- Set fileSystem = New FileSystemObject creates a new instance of the FileSystemObject and assigns it to the “fileSystem” variable.
For Each file In folder.Files
Range("B" & currentRow).Value = file.Name
Range("C" & currentRow).Value = file.Type
Range("D" & currentRow).Value = file.Size
' Increment the current row
currentRow = currentRow + 1
Next- This part starts a loop that will execute once for each file in the “Files” collection of the “fileSystem” object.
- The code stores the file names, types, and sizes in columns B, C, and D respectively.
Set fileSystem = Nothing
Set folder = Nothing- These two lines are used to release memory used by FileSystemObject.
Press F5 or click the Run button to get your desired output in the Excel worksheet.
Read More: Excel VBA to Count Files in Folder and Subfolders
1.2 Use of Dir Function
In this method, we will the Dir Function to list file names in an Excel worksheet. We will use the VBA code below to list file names, file types, and sizes.
Sub ListFilesInDirectory()
Dim folderPath As String
Dim currentFileName As String
Dim currentFileType As String
Dim currentFileSize As Long
Dim currentRowCounter As Integer
folderPath = "E:\Softeko\UDF" ' Specify the folder path
currentFileName = Dir(folderPath) ' Get the first file in the folder
currentRowCounter = 5 ' Starting row for output
While currentFileName <> ""
' Extract the file type and size
currentFileType = Right(currentFileName, Len(currentFileName) _
- InStrRev(currentFileName, "."))
currentFileSize = FileLen(folderPath & currentFileName)
' Print the file name, type, and size to
'columns B, C, and D starting from row 5
Range("B" & currentRowCounter).Value = currentFileName
Range("C" & currentRowCounter).Value = currentFileType
Range("D" & currentRowCounter).Value = currentFileSize
currentRowCounter = currentRowCounter + 1
currentFileName = Dir ' Get the next file in the folder
Wend
End Sub
Code Breakdown
folderPath = "E:\Softeko\UDF\"
currentfileName = Dir(folderPath)- The first line declares a variable called folderPath and assigns it to “E:\Softeko\UDF\”.
- The second line creates a variable named currentfileName and assigns the value returned by the Dir() function.
currentfileType = Right(currentfileName, Len(fileName) - InStrRev(fileName, "."))
currentfileSize = FileLen(folderPath & currentfileName)- currentfileType = Right(fileName, Len(currentfileName) – InStrRev(currentfileName, “.”)) line declares a variable name fileType. The Right function extracts the last portion of the currentfileName starting from the “.”.
- currentfileSize = FileLen(folderPath & currentfileName) this line calculates the file size by concatenating folderPath and currentfileName variables together.
Finally, run the code. As a result, you will find all the file names, types, and sizes listed in your worksheet.
1.3 Using File System Object Late Binding
Now we will use the FSO Late Binding for the same purpose as above. Type the following code in the Microsoft Visual Basic code Module.
Sub ListFiles()
Dim folderName As String
Dim FSOLibrary As Scripting.FileSystemObject
Dim FSOFolder As Scripting.folde
Dim FSOFile As Scripting.file
Dim outputSheet As Worksheet
Dim rowIndex As Long
'set the folder name to a variable
folderName = "E:\Softeko\UDF\"
Set FSOLibrary = New Scripting.FileSystemObject
Set FSOFolder = FSOLibrary.GetFolder(folderName)
' set the output to the sheet named "Late"
Set outputSheet = ThisWorkbook.Sheets("Late")
rowIndex = 5 ' set the starting row index to 5
For Each FSOFile In FSOFolder.files
' output the file name to column B, Type to Column C
'and Size to Column E
outputSheet.Cells(rowIndex, 2).Value = FSOFile.Name
outputSheet.Cells(rowIndex, 3).Value = FSOFile.Type
outputSheet.Cells(rowIndex, 4).Value = FSOFile.Size
rowIndex = rowIndex + 1 ' increment the row index
Next
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set outputSheet = Nothing
End Sub
Code Breakdown
Set FSOLibrary = New Scripting.FileSystemObject
Set FSOFolder = FSOLibrary.GetFolder(folderName)- Set FSOLibrary = New Scripting.FileSystemObject creates an instance of the FileSystemObject class from the Microsoft Scripting Runtime Library and assigns it to the FSOLibrary variable using the New
- The GetFolder() method of the FileSystemObject object is used to obtain a reference to a specific folder in the file system, specified by folderName, which is a string variable containing the full path to the desired folder.
For Each FSOFile In FSOFolder.files
' output the file name to column B, Type to Column C
'and Size to Column E
outputSheet.Cells(rowIndex, 2).Value = FSOFile.Name
outputSheet.Cells(rowIndex, 3).Value = FSOFile.Type
outputSheet.Cells(rowIndex, 4).Value = FSOFile.Size
rowIndex = rowIndex + 1 ' increment the row index
Next- The first line starts a loop that iterates over all the files in the folder referenced by FSOFile.
- The next three lines store file names, types, and sizes in columns B, C, and D.
After writing the code, run it by pressing F5 on your keyboard to get the list of all files.
Read More: Excel VBA to List Files in Folder with Specific Extension
2. List All Files of a Folder and Subfolder in Worksheet
Now we want all the files from both folders and subfolders in our worksheet. We will list the file names, file types, and sizes in this method. We will use the recursive method in the following examples.
2.1 Use File System Object Early Binding
First, we will use the File System Object Early Binding to list files from all subfolders. Follow these steps to learn how to do it.
- Go to the Microsoft Visual Basic window and insert a Module.
- Copy the code given below and paste it into the Module.
Sub ListFilesSubfoldersEarly(FSOFolder As Object, _
ws As Worksheet, rowNum As Long)
Dim FSOSubFolder As Object
Dim FSOFile As Object
'call the macro ListFilesSubfoldersEarly
For Each FSOSubFolder In FSOFolder.subFolders
ListFilesSubfoldersEarly FSOSubFolder, ws, rowNum
Next
'Print file names, types and size
For Each FSOFile In FSOFolder.files
ws.Cells(rowNum, 2).Value = FSOFile.path
ws.Cells(rowNum, 3).Value = FSOFile.Type
ws.Cells(rowNum, 4).Value = FSOFile.Size
rowNum = rowNum + 1
Next
End Sub
Sub SubfoldersStartDirectory()
Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim folderName As String
Dim ws As Worksheet
Dim rowNum As Long
'Set the folder name to a variable
folderName = "E:\Softeko\UDF\"
Set FSOLibrary = New FileSystemObject
'Set the worksheet and starting row number
Set ws = ThisWorkbook.Worksheets("Early2")
rowNum = 5
'Call the ListFilesSubfoldersEarly Macro
ListFilesSubfoldersEarly FSOLibrary.GetFolder(folderName), _
ws, rowNum
End Sub
Code Breakdown
For Each FSOSubFolder In FSOFolder.subFolders
ListFilesSubfoldersEarly FSOSubFolder, ws, rowNum
Next- This part calls the macro named ListFilesSubfoldersEarly.
For Each FSOFile In FSOFolder.files
ws.Cells(rowNum, 2).Value = FSOFile.path
ws.Cells(rowNum, 3).Value = FSOFile.Type
ws.Cells(rowNum, 4).Value = FSOFile.Size
rowNum = rowNum + 1
Next- This part lists the file names, types, and sizes in columns B, C, and D.
- After that, run the code and get your desired output.
2.2 Using File System Object Late Binding
In this example, we will list all files from subfolders using File System Late Binding. We will insert the following code in the code Module for this purpose.
Sub ListAllFiles()
Dim fso As Object
Dim folder As Object
Dim file As Object
Dim folderName As String
Dim currentRow As Long
' Set the folder name to a variable
folderName = "E:\Softeko\UDF\"
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
currentRow = 5
Set fso = CreateObject("Scripting.FileSystemObject")
' Call the recursive function to list all files
ListFilesSubfolders fso.GetFolder(folderName), currentRow, ws
End Sub
Sub ListFilesSubfolders(fsoFolder As Object, ByRef currentRow As Long, _
ws As Worksheet)
Dim subFolder As Object
Dim file As Object
For Each subFolder In fsoFolder.subFolders
ListFilesSubfolders subFolder, currentRow, ws
Next subFolder
For Each file In fsoFolder.Files
ws.Cells(currentRow, 2).Value = file.Name
ws.Cells(currentRow, 3).Value = file.Type
ws.Cells(currentRow, 4).Value = file.Size
currentRow = currentRow + 1
Next file
End Sub
Code Breakdown
Sub ListFilesSubfolders(fsoFolder As Object, ByRef currentRow As Long, _
ws As Worksheet)
Dim subFolder As Object
Dim file As Object
For Each subFolder In fsoFolder.subFolders
ListFilesSubfolders subFolder, currentRow, ws
Next subFolder
For Each file In fsoFolder.Files
ws.Cells(currentRow, 2).Value = file.Name
ws.Cells(currentRow, 3).Value = file.Type
ws.Cells(currentRow, 4).Value = file.Size
currentRow = currentRow + 1
Next file
End Sub- The code first declares two variables named FSOSubFolder and FSOFile as object.
- Then it loops through the subfolders in the FSOFolder parameter.
- ListFilesSubfoldersLate FSOSubFolder, currentRow calls the ListFilesSubfoldersLate subroutine recursively, passing in the FSOSubFolder parameter as the new FSOFolder parameter and the currentRow parameter unchanged. The code then stores outputs in the specified cells.
Finally, we will run the code and get the list of all files from the folder and subfolders.
3. List Files in Folders and Subfolders Using Non-Recursive VBA Method
In the above examples, we used the recursive method to list files in folders and subfolders. Now we will use the non-recursive method for the same purpose. The VBA code to do this is given below.
Sub ListFilesNonrecursive()
Dim fso As Objec
Dim folder As Object
Dim subFolder As Object
Dim file As Object
Dim i As Integer
Dim ws As Worksheet
Set ws = ActiveSheet
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("E:\Softeko\UDF\")
For Each file In folder.files
If file.DateLastModified > Now - 7 Then
ws.Cells(i + 5, 2) = file.Name
ws.Cells(i + 5, 3) = file.Type
i = i + 1
End If
Next file
For Each subFolder In folder.subFolders
For Each file In subFolder.files
If file.DateLastModified > Now - 7 Then
ws.Cells(i + 5, 2) = file.Name
ws.Cells(i + 5, 3) = file.Type
i = i + 1
End If
Next file
Next subFolder
End Sub
Code Breakdown
For Each file In folder.files
If file.DateLastModified > Now - 7 Then
ws.Cells(i + 5, 2) = file.Name
ws.Cells(i + 5, 3) = file.Type
i = i + 1
End If
Next file- The code checks if the file was modified within the last week. If it is modified in the last week, the code stores file names and types in columns B and C.
After running the code, the output will be looking like the image below.
How to List Files in Folders and Subfolders with Hyperlinks with Excel VBA
In this example, we will list all files of a folder and subfolders with hyperlinks. You can click on these hyperlinks to open the files. The path of our folder is “E:\Softeko\UDF\”. You can use the following code to list files and add hyperlinks.
Sub ListFilesHyperlink()
Dim fso As Object, folder As Object, subFolder As Object, _
file As Object
Dim path As String, row As Long
path = "E:\Softeko\UDF\"
row = 5
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(path)
' Output files in the main folder
For Each file In folder.files
Cells(row, 2).Value = file.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(row, 2), _
Address:=file.path
row = row + 1
Next file
' Output files in subfolders
For Each subFolder In folder.subFolders
For Each file In subFolder.files
Cells(row, 2).Value = file.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(row, 2), _
Address:=file.path
row = row + 1
Next file
Next subFolder
Set fso = Nothing
Set folder = Nothing
End Sub
Code Breakdown
For Each file In folder.files
Cells(row, 2).Value = file.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(row, 2), _
Address:=file.path
row = row + 1
Next file- Hyperlinks.Add Anchor:=Cells(row, 2), Address:=file.path adds a hyperlink to the file’s path in the cell that was just written to in the previous line. This creates a clickable link that will open the file when clicked.
Once you run the code, the list of files will be stored in the worksheet with hyperlinks.
How to List Files in Folder with Specific Extensions in Excel with VBA
If you want to list files with specific extensions, you can use the following VBA code. This code list all the files with the .png and .xlsx extensions. You can change the extensions as per your need.
Sub ListFiles()
Dim fso As Object, folder As Object, subFolder As Object, _
file As Object
Dim path As String, row As Long
path = "E:\Softeko\UDF\"
row = 5
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(path)
' Output files in the main folder
For Each file In folder.files
If file.Name Like "*.png" Or file.Name Like "*.xlsx" Then
Cells(row, 2).Value = file.Name
Cells(row, 3).Value = file.Type
Cells(row, 4).Value = file.Size
row = row + 1
End If
Next file
' Output files in subfolders
For Each subFolder In folder.subFolders
For Each file In subFolder.files
If file.Name Like "*.png" Or file.Name Like "*.xlsx" Then
Cells(row, 2).Value = file.Name
Cells(row, 3).Value = file.Type
Cells(row, 4).Value = file.Size
row = row + 1
End If
Next file
Next subFolder
Set fso = Nothing
Set folder = Nothing
End Sub
Code Breakdown
For Each file In folder.files
If file.Name Like "*.png" Or file.Name Like "*.xlsx" Then
Cells(row, 2).Value = file.Name
Cells(row, 3).Value = file.Type
Cells(row, 4).Value = file.Size
row = row + 1
End If
Next file- For each file, the code checks if the file’s name matches either the pattern “*.png” or “*.xlsx” using the Like
- If it finds a match, it stores the file names, types, and sizes in the specified columns.
Run the code to get your desired output. Only the .png and .xlsx files will be listed.
How to List Files in Folders and Subfolders in a Text File Using VBA
In this example, we will use the following VBA code to list files in a folder and subfolders in a text file.
Sub List_All_Files_Text()
PID = Shell("cmd /c dir E:\Softeko\UDF /s /b > E:\Softeko\UDF\Files_List.txt", _
vbHide)
Do While IsFileInUse("E:\Softeko\UDF\Files_List.txt")
DoEvents
Loop
End Sub
Function IsFileInUse(filePath As String) As Boolean
On Error Resume Next
Open filePath For Binary Access Read Write Lock Read Write As #1
Close #1
IsFileInUse = Err.Number <> 0
On Error GoTo 0
End Function
Code Breakdown
PID = Shell("cmd /c dir E:\Softeko\UDF /s /b > E:\Softeko\UDF\Files_List.txt", _
vbHide)
Do While IsFileInUse("E:\Softeko\UDF\Files_List.txt")
DoEvents
Loop- PID = Shell(“cmd /c dir E:\Softeko\UDF /s /b > E:\Softeko\UDF\Files_List.txt”, vbHide) executes the command dir E:\Softeko\UDF /s /b > E:\Softeko\UDF\Files_List.txt in the command prompt. The /s specifies that the command should be executed recursively on all subdirectories, while the /b specifies that only the file names should be displayed, without any additional information. The output of the command is redirected to a text file named txt in the E:\Softeko\UDF directory. The vbHide parameter specifies that the command prompt window should be hidden.
- Do While IsFileInUse(“E:\Softeko\UDF\Files_List.txt”) DoEvents Loop loops continuously until the txt file is no longer in use.
Run the code and go to the location you have chosen for the text file.
Then open the text file and you will find all the files from the folder and subfolders listed there.
Things to Remember
- Make sure you have checked the Microsoft Scripting Runtime box in the reference library.
- The Dir function methods are an integral part of VBA and do not require any extra libraries. On the other hand, the FSO methods need either early or late binding to ensure their proper execution.
Download Practice Workbook
Download this practice book while reading this article.
Conclusion
Thanks for making it this far. I hope you found this article useful. In this article, we have demonstrated three different methods of using Excel VBA to list files in folder and subfolders in worksheets. We have used both recursive and non-recursive methods. For the recursive methods, we have shown the use of the Dir function, FSO early and late binding to achieve the same goal. Here, I also covered how to listing files in a text file in this article.
If you have any queries or recommendations regarding this article, don’t forget to let us know in the comment section below.
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
