VBA Loop Through all Files in subfolders using FSO in Excel

VBA Loop Through all Files in subfolders using File System Object(FSO) and built-in Dir() function. VBA for each file in sub-folder macro helps us to loop through all files in sub-folders. In this tutorial we loop through sub-folders and list all available files in sub-folders using FSO early-binding and late-binding method and Dir() function.

There are two approaches to use FSO object library.
1. Early Binding
2. Late Binding

Table of Contents:

Objective
Loop through all files in sub-folders using FSO – Early Binding in Excel VBA

Add Early Binding Reference

List all files in sub-folders using FSO – Late Binding in Excel VBA
Instructions to Run VBA Macro Code
Other Useful Resources

Loop through all files in sub-folders using FSO – Early Binding in Excel VBA

Let us see example on Loop through all files in sub-folders using FSO – Early Binding in Excel VBA.

'Loop Through all Files in sub-folders  using FSO early Binding in Excel VBA
Sub VBA_Loop_Through_all_Files_in_subfolders_Using_FSO_Early_Binding()

    'Variable Declaration
    Dim oFSO As FileSystemObject, oFolder As Object
    Dim oSubFolders As Object, oSFolderFile As Object
    Dim sFile As Object, sfolders As Object
    Dim Wbook As Workbook
    Dim i  As Integer
    
    'Initialize value
    i = 2
    
    'Declare headers
    With Sheet1
        .Range("A1") = "SubFolder Name"
        .Range("B1") = "File Name"
    End With
    
    'Set objects
    Set oFSO = New FileSystemObject
    Set oFolder = oFSO.GetFolder("C:\VBAF1")
    Set oSubFolders = oFolder.subfolders
    
    'Loop through subfolders
    For Each sfolders In oSubFolders
         Sheet1.Range("A" & i) = sfolders.Name
        Set oSFolderFile = sfolders.Files
        
        'Loop through all files in a subfolder
        For Each sFile In oSFolderFile
            Sheet1.Range("B" & i) = sFile.Name
            i = i + 1
        Next
    Next
    
    'Release the memory
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oSubFolders = Nothing
    Set oSFolderFile = Nothing

End Sub

Note: If you get any error while executing above macro follow the below specified instructions to add FSO library reference.

Add Early Binding Reference

Here are the instructions to add early binding reference.

Go to Tools from VBE menu.
Click on references from the available options.
Check the Microsoft scripting Runtime.
Click on OK.
Please find the below screenshot for your reference.

List all files in subfolders using FSO – Late Binding in Excel VBA

Let us see example on Loop through all files in subfolders using FSO – Late Binding in Excel VBA.

'Loop Through all Files in subfolders using FSO late Binding
Sub VBA_Loop_Through_all_Files_in_subfolders_Using_FSO_Late_Binding()

    'Variable Declaration
    Dim oFSO As Object, oFolder As Object
    Dim oSubFolders As Object, oSFolderFile As Object
    Dim sFile As Object, sfolders As Object
    Dim Wbook As Workbook
    Dim i  As Integer
    
    'Initialize value
    i = 2
    
    'Declare headers
    With Sheet1
        .Range("A1") = "SubFolder Name"
        .Range("B1") = "File Name"
    End With
    
    'Set objects
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("C:\VBAF1")
    Set oSubFolders = oFolder.subfolders
    
    'Loop through subfolders
    For Each sfolders In oSubFolders
         Sheet1.Range("A" & i) = sfolders.Name
        Set oSFolderFile = sfolders.Files
        
        'Loop through all files in a subfolder
        For Each sFile In oSFolderFile
            Sheet1.Range("B" & i) = sFile.Name
            i = i + 1
        Next
    Next
    
    'Release the memory
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oSubFolders = Nothing
    Set oSFolderFile = Nothing

End Sub

Output: Here is the output screenshot of above macro code procedure.