Listing files in a worksheet, printing all the files to PDF, or making changes to every file in a folder, there are many reasons why we may want to loop through each file in a folder. The macros below cover two scenarios
- Looping through all files in a folder
- Looping through all files in a folder and it’s subfolders
Looping through all the files in a folder
Of the two Macros in this post, this is the easiest to understand. This example will only print the file name to the immediate window. But it is possible to change the code to make it significantly more complex. Check out the other VBA Code Snippets to see what else could be achieved.
'Loop through all files in a folder Dim fileName As Variant fileName = Dir("C:\Users\marks\Documents\") While fileName <> "" 'Insert the actions to be performed on each file 'This example will print the file name to the immediate window Debug.Print fileName 'Set the fileName to the next file fileName = Dir Wend
The code above can easily be adapted with the use of wildcard characters. For example:
'Loop through each file with an extension of ".xlsx" fileName = Dir("C:\Users\marks\Documents\*.xlsx")
'Loop through each file containing the word "January" in the filename fileName = Dir("C:\Users\marks\Documents\*January*")
'Loop through each text file in a folder fileName = Dir("C:\Users\marks\Documents\*.txt")
Looping through all the files subfolders
This Macro is significantly more complex. To ensure the code works with any number of subfolders, the macro will actually call itself.
This example will print the full file path (folder path and file name) to the immediate window.
Sub loopAllSubFolderSelectStartDirector() 'Another Macro must call LoopAllSubFolders Macro to start to procedure Call LoopAllSubFolders("C:\Users\marks\Documents\") End Sub
'List all files in sub folders Sub LoopAllSubFolders(ByVal folderPath As String) Dim fileName As String Dim fullFilePath As String Dim numFolders As Long Dim folders() As String Dim i As Long If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\" fileName = Dir(folderPath & "*.*", vbDirectory) While Len(fileName) <> 0 If Left(fileName, 1) <> "." Then fullFilePath = folderPath & fileName If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then ReDim Preserve folders(0 To numFolders) As String folders(numFolders) = fullFilePath numFolders = numFolders + 1 Else 'Insert the actions to be performed on each file 'This example will print the full file path to the immediate window Debug.Print folderPath & fileName End If End If fileName = Dir() Wend For i = 0 To numFolders - 1 LoopAllSubFolders folders(i) Next i End Sub