VBA List Files in Folder

In this tutorial, you will learn how to get names of all files in a folder and put them into a Worksheet.

Instead, if you want to learn how to check if a file exists, you can click on this link: VBA File Exists 

 

Using the FileSystemObject to Get the List of Files in a Folder

VBA allows you to list all files from a folder, using the FileSystemObject.

We will show how to get a list of files in the folder C:\VBA Folder and put it into the first column of the Worksheet. This folder consists of 5 files, as shown in Image 1:

Image 1. Files in folder C:\VBA Folder

 

Here is the code:

Sub LoopThroughFiles ()

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFolder = oFSO.GetFolder("C:\VBA Folder")

For Each oFile In oFolder.Files

    Cells(i + 1, 1) = oFile.Name

    i = i + 1

Next oFile

End Sub

 

In the example, first create an object of the class Scripting.FileSystemObject:

Set oFSO = CreateObject("Scripting.FileSystemObject")

 

Then set the folder using the method GetFolder:

Set oFolder = oFSO.GetFolder("C:\VBA Folder")

 

Next loop through each file in oFolder, using oFile.Name to get the name of every file in the folder and write it in the next empty row:

For Each oFile In oFolder.Files

    Cells(i + 1, 1) = oFile.Name
    i = i + 1

Next oFile

 

Image 2. Worksheet with the list of files in the folder

 

As you can see in Image 2, all 5 files from the C:\VBA Folder are listed in the first column.