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