This post may contain affiliate links. Please read my disclosure for more info:

VBA code to loop through files in a folder (and sub folders)

VBA Code Snippets

VBA Code Snippets

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

  1. Looping through all files in a folder
  2. 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

11 thoughts on “VBA code to loop through files in a folder (and sub folders)

    • Excel Off The Grid says:

      Thanks Yasser, I appreciate the feedback. I’m focusing on this section for a few months, so soon there will be some more treasures for you to find.

    • Excel Off The Grid says:

      Hi Yasser,

      I have not set up the website to automatically send notifications when replies are posted. I will look into it.

      Thanks,

      Mark (that’s my name)

  1. Nick says:

    I’m having a problem when looping through I’m running other macros. All is going well except when I try and publish pdfs it stops after the first loop, any help?

    • Excel Off The Grid says:

      Hi MaX

      I’m assuming you’re referring to the files in subfolders macro. Change this line of code:

      Debug.Print folderPath & fileName

      To this:

      If InStrRev(fileName, “.xlsx”) Then
      Debug.Print folderPath & fileName
      End If

      Now it will only list .xlsx files.

Leave a Reply

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