VBA Loop Through all Files in a Folder

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

List all files in a folder using Dir() Function in Excel VBA

Let us see the example macro on VBA List all files in a folder using Dir() Function. As a result it displays output in the immediate window.

'VBA List all files in a folder using Dir
Sub VBAF1_List_All_Files_In_A_Folder_Using_Dir()
    
    'Variable Declaration
    Dim sFilePath As String
    Dim sFileName As String
    
    'Specify File Path
    sFilePath = "D:\VBAF1"
    
    'Check for back slash
    If Right(sFilePath, 1) <> "\" Then
        sFilePath = sFilePath & "\"
    End If
        
    sFileName = Dir(sFilePath)
    
    Do While Len(sFileName) > 0
        
        'Display file name in immediate window
        Debug.Print sFileName
        
        'Set the fileName to the next available file
        sFileName = Dir
    Loop
   
End Sub

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

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

Sub VBAF1_List_All_Files_In_A_Folder_Using_FSO_Early_Binding()
    
    'Variable Declaration
    Dim fsoLibrary As FileSystemObject
    Dim fsoFolder As Object
    Dim sFolderPath As String
    Dim sFileName As Object
    
    'Set the folder path to a variable
    sFolderPath = "D:\VBAF1\"
    
    'Set all the references to the FSO Library
    Set fsoLibrary = New FileSystemObject
    Set fsoFolder = fsoLibrary.GetFolder(sFolderPath)
    
    'Loop through each file in a folder
    For Each sFileName In fsoFolder.Files
        Debug.Print sFileName.Name
    Next
    
    'Release the memory
    Set fsoLibrary = Nothing
    Set fsoFolder = 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.
FSO Microsoft Scripting Runtime

List all files in a folder using FSO – Late Binding in Excel VBA

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

Sub VBAF1_List_All_Files_In_A_Folder_Using_FSO_Late_Binding()
    
    'Variable Declaration
    Dim FSOLibrary As FileSystemObject
    Dim FSOFolder As Object
    Dim sFolderPath As String
    Dim sFileName As Object
    
    'Set the folder path to a variable
    sFolderPath = "D:\VBAF1\"
    
    'Set all the references to the FSO Library
    Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
    Set FSOFolder = FSOLibrary.GetFolder(sFolderPath)
    
    'Loop through each file in a folder
    For Each sFileName In FSOFolder.Files
        Debug.Print sFileName.Name
    Next
    
    'Release the memory
    Set FSOLibrary = Nothing
    Set FSOFolder = Nothing

End Sub

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

Loop Through all Files in a Folder

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

2 thoughts on “VBA Loop Through all Files in a Folder”

  1. Magnificent beat ! I wish to apprentice while you amend your site, how can i subscribe
    for a weblog website? The account helped me a acceptable deal.
    I have been a little bit familiar of this your broadcast
    provided vivid transparent concept

  2. Hi there, this weekend is nice in favor of me, for the reason that this point in time i am
    reading this wonderful educational post here at my residence.

Leave a Comment