VBA Loop Through all Files in a Folder

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. In this tutorial we list all available files in a folder using FSO early-binding and late-binding 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 VBA 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 VBA Loop through all files in a folder using FSO – Late Binding.

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.

VBA Loop Through all Files in a Folder

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
  • You can see output on the screen
  • Find above specified output screenshot.

Other Related VBA Arrays articles

You may also like the related VBA Array articles.

VBA File and Directory Functions VBA Tutorial




Leave a Comment

Your email address will not be published. Required fields are marked *