VBA Loop Through all Files in subfolders

VBA Loop Through all Files in subfolders using File System Object(FSO) and built-in Dir() function. VBA for each file in subfolder macro helps us to loop through all files in subfolders. In this tutorial we loop through subfolders and list all available files in subfolders 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

Loop through all files in subfolders using FSO – Early Binding in Excel VBA

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

'VBA Loop Through all Files in subfolders  using FSO early Binding
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 VBA Loop through all files in subfolders using FSO – Late Binding.

'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.

Instructions to use Macro

Here are the instructions to use above macro in Visual basic editor.

• Open Visual Basic Editor(VBE) by clicking Alt +F11
• Go to code window by clicking F7
• Copy above specified macro or procedure
• Paste above copied code in code window
• Run macro by clicking F5 or Run command
• Find above specified output in Sheet1.