How to 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 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: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 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.

How to Create Folders Using VBA