Table of Contents
VBA, or Visual Basic for Applications, is a programming language that can be used within Microsoft Office applications to automate tasks and processes. One useful task that can be performed using VBA is listing the files in a folder. This can be achieved by utilizing the “FileSystemObject” and “File” objects in VBA, which allow for the manipulation and management of files and folders. By using these objects, the code can loop through all the files in a folder and display their names, sizes, and other properties. This functionality can be particularly beneficial for organizing and managing large numbers of files within a folder. Overall, listing files in a folder using VBA can greatly improve efficiency and productivity in various tasks and projects.
VBA: List Files in Folder
You can use the following methods in VBA to list files in a folder:
Method 1: List All Files in Folder
Sub ListFiles()
Dim i As Integer
Dim oFSO As Object
Dim oFolder As Object
Dim objFile As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("C:UsersbobDocumentscurrent_data")
For Each objFile In oFolder.Files
Cells(i + 1, 1) = objFile.Name
i = i + 1
Next objFile
End Sub
Method 2: List Only .xlsx Files in Folder
Sub ListFiles()
Dim i As Integer
Dim oFSO As Object
Dim oFolder As Object
Dim objFile As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("C:UsersbobDocumentscurrent_data")
For Each objFile In oFolder.Files
If Right(objFile.Name, 4) = "xlsx" Then
Cells(i + 1, 1) = objFile.Name
i = i + 1
End IfNext objFile
End Sub
The following examples show how to use each method in practice with a folder that has the following file path:
- C:UsersbobDocumentscurrent_data
The folder contains two .xlsx files and three .csv files:
Example 1: List All Files in Folder
We can create the following macro to list all files in the folder:
Sub ListFiles()
Dim i As Integer
Dim oFSO As Object
Dim oFolder As Object
Dim objFile As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("C:UsersbobDocumentscurrent_data")
For Each objFile In oFolder.Files
Cells(i + 1, 1) = objFile.Name
i = i + 1
Next objFile
End Sub
When we run this macro, we receive the following output:
We can see that the names of all files in the folder (regardless of their extension) are now listed in column A of our Excel sheet.
Example 2: List Only .xlsx Files in Folder
We can create the following macro to list only the files that have a .xlsx extension in the folder:
Sub ListFiles()
Dim i As Integer
Dim oFSO As Object
Dim oFolder As Object
Dim objFile As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("C:UsersbobDocumentscurrent_data")
For Each objFile In oFolder.Files
If Right(objFile.Name, 4) = "xlsx" Then
Cells(i + 1, 1) = objFile.Name
i = i + 1
End IfNext objFile
End Sub
When we run this macro, we receive the following output:
We can see that the names of only the files with a .xlsx extension in the folder are now listed in column A of our Excel sheet.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: