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.