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:\Users\bob\Documents\current_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:\Users\bob\Documents\current_data")
For Each objFile In oFolder.Files
If Right(objFile.Name, 4) = "xlsx" Then
Cells(i + 1, 1) = objFile.Name
i = i + 1
End If
Next objFile
End Sub
The following examples show how to use each method in practice with a folder that has the following file path:
- C:\Users\bob\Documents\current_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:\Users\bob\Documents\current_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:\Users\bob\Documents\current_data")
For Each objFile In oFolder.Files
If Right(objFile.Name, 4) = "xlsx" Then
Cells(i + 1, 1) = objFile.Name
i = i + 1
End If
Next 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:
How to Create Folders Using VBA
How to Delete Folders Using VBA
How to Rename a File Using VBA
How to Check if File Exists Using VBA
