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
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
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.
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.
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.
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.