This post may contain affiliate links. Please read my disclosure for more info.
Power Query Course

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

VBA Code Snippets

Listing filenames 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.  VBA provides us with a few ways to achieve it (1) Dir function (2) File System Object.

Let’s briefly look at each of these before we start looking at the code to loop through files.

Using the Dir Function

The Dir function is a built-in VBA function, meaning it works with Excel, PowerPoint and Word; In fact, it will work anywhere where VBA is available.

The Dir function is easy to use and does not require any special actions to enable it within the Visual Basic Editor.

Using the File System Object (FSO)

The File System Object (FSO) is a separate library of actions which we can enable to use within our VBA code.  There are two ways to turn on the FSO library, known as Early or Late Binding.  If you’re not sure which to use, then I recommend using Late Binding.  It may result in slower code, but it should run with less errors.

Early Binding

Early Binding will open the FSO library as soon as the workbook opens.

In the Visual Basic Editor, click Tools -> References…

VBE Tools References

From the References – VBAProject window select the Microsoft Scripting Runtime option, then click OK.

Microsoft Scripting Runtime Reference

Late Binding

Late Binding does not require any specific actions to enable the FSO Library.  We will ensure the library is opened when we need it within the VBA code.

Examples

The example 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

Each of these scenarios will use the Dir, Late Binding FSO and Early Binding FSO options.

Looping through all the files in a folder

The example we are looking will print the file name to the immediate window.  But it is easy enough to change the code to make it more complex and fit with your specific circumstances.  Check out the other VBA Code Snippets to see what else could be achieved.

Using the Dir Function – Method #1

Sub LoopAllFilesInAFolder()

'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

End Sub

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")

Using the File System Object (FSO) Late Binding – Method #2

Sub LoopAllFilesInFolder()

Dim folderName As String
Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object

'Set the file name to a variable
folderName = "C:\Users\marks\Documents\"

'Set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(folderName)
Set FSOFile = FSOFolder.Files

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile

    'Insert actions to be perfomed on each file
    'This example will print the file name to the immediate window
    Debug.Print FSOFile.Name

Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set FSOFile = Nothing

End Sub

Using File System Object (FSO) – Early Binding – Method #3

To use this method, remember to turn on the FSO Library as noted above.  Most of the code is the same as Method #2, but for the sake of the copy and pasters out there, I’ll show the full code.

Sub LoopFilesInFolder()

Dim folderName As String
Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim FSOFile As Object

'Set the file name to a variable
folderName = "C:\Users\marks\Documents\"

'Set all the references to the FSO Library
Set FSOLibrary = New FileSystemObject
Set FSOFolder = FSOLibrary.GetFolder(folderName)
Set FSOFile = FSOFolder.Files

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile

    'Insert actions to be perfomed on each file
    'This example will print the file name to the immediate window
    Debug.Print FSOFile.Name

Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set FSOFile = Nothing

End Sub

Looping through all the files in subfolders

Whenever files are stored in subfolders life becomes a little trickier.  We now need to find a way to drill down into those subfolders.  We will use the same Dir and FSO methods presented above.  To ensure the codes work with any number of subfolders, the macro will actually call itself (a technique known as recursion).

Using the DIR function

This example will print the full file path (folder path and file name) to the immediate window.

Run the following macro:

Sub loopAllSubFolderSelectStartDirectory()

'Another Macro must call LoopAllSubFolders Macro to start to procedure
Call LoopAllSubFolders("C:\Users\marks\Documents\")

End Sub

Don’t run the following macro, it will be called from the macro above:

'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

Using File System Object (FSO) Late Binding – Method #2

Run the following macro:

Sub loopAllSubFolderSelectStartDirectory()

Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim folderName As String

'Set the folder name to a variable
folderName = "C:\Users\marks\Documents\"

'Set the reference to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")

'Another Macro must call LoopAllSubFolders Macro to start
LoopAllSubFolders FSOLibrary.GetFolder(folderName)

End Sub

Don’t run the following macro, it will be called from the macro above

Sub LoopAllSubFolders(FSOFolder As Object)

Dim FSOSubFolder As Object
Dim FSOFile As Object

'For each subfolder call the macro
For Each FSOSubFolder In FSOFolder.subfolders
    LoopAllSubFolders FSOSubFolder
Next

'For each file, print the name
For Each FSOFile In FSOFolder.Files

    'Insert the actions to be performed on each file
    'This example will print the full file path to the immediate window
    Debug.Print FSOFile.Path

Next

End Sub

Using File System Object (FSO) Late Binding – Method #3

To use this method, remember to turn on the FSO Library as noted above.  Most of the code here is the same as method 2, but I will show it in full, so you can copy and paste.

Run the following macro:

Sub loopAllSubFolderSelectStartDirectory()

Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim folderName As String

'Set the folder name to a variable
folderName = "C:\Users\marks\OneDrive\Documents\"

'Set the reference to the FSO Library
Set FSOLibrary = New FileSystemObject

'Another Macro must call LoopAllSubFolders Macro to start
LoopAllSubFolders FSOLibrary.GetFolder(folderName)

End Sub

Don’t run the following macro, it will be called from the macro above:

Sub LoopAllSubFolders(FSOFolder As Object)

Dim FSOSubFolder As Object
Dim FSOFile As Object

'For each subfolder call the macro
For Each FSOSubFolder In FSOFolder.subfolders
    LoopAllSubFolders FSOSubFolder
Next

'For each file, print the name
For Each FSOFile In FSOFolder.Files

    'Insert the actions to be performed on each file
    'This example will print the full file path to the immediate window
    Debug.Print FSOFile.Path

Next

End Sub

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

  1. Yasser says:

    I am glad to hear about that.
    Can I know your name if you don’t mind of course?

    Why didn’t I receive emails about replies?!

    • 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)

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

  3. FO says:

    Hi,

    When I use the suggested:

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

    I get: error 424 Object Required

    Any suggestions?

    • Excel Off The Grid says:

      Hi FO,

      Does it output anything to the Immediate Window, or does it error straight away?

      Are you able to provide a list showing how the folder & sub-folders are listed.

      Thanks,

      Mark

      • Excel Off The Grid says:

        Hi Frank,

        As per my e-mail, I think the issue is with how the web browser displays quotation/speech marks. In this comments section, it appears to convert them into different characters to look more like upon and close quotes.

        After you copy and paste the code in the comment above, change the quotation marks to the normal ones which are on your keyboard.

        Let me know if it works.

  4. Yogi says:

    You are a genius Mark, It took me about 2 hours to understand this code thoroughly, But I was stunned to see the logic you have written, Fantastic code it is, Just curious to know how many hours it took to develop this code & logic.

    Thanks a ton for this code & logic. It’s very helpful.

    • Excel Off The Grid says:

      Hi Yogi,

      I wish I could take full credit. I originally found a similar code in a colleagues macro. And much like you, I spent a long time working through it over and over again. Once I understood the principles, I tried to simplify it down until I knew exactly what it was doing.

      Then I discovered the FileSystemObject and realized that is probably a better option. I’ve not covered the FileSystemObject on the site yet, but it’s worth checking out as an easier alternative.

      As I was writing this post about using Dir(), I thought it would be interesting to others.

      • Yogi says:

        Thank you for your quick response Mark, you are very knowledgeable & humble too. You have done great help to most of the people by writing or getting it through various sources. Your efforts are remarkable. Thanks again & all the best.

  5. Steve says:

    Mark – your site is great, thanks. I have Looping through all the files subfolders working perfectly as is.

    Can’t make it do what I’m after though and that is to copy every file in each folder and subfolder to a different folder. I’ve tried every method i cant think of from FileCopy to FSO.CopyFile but just cannot make it happen I think because I’m trying to create a filename from each I end up with type errors or object errors that worked in other situations.

    Any thoughts would be much appreciated.

    Thanks!

    • Excel Off The Grid says:

      Hi Steve,

      It’s not something I’ve tried myself.

      As it’s causing you issues, I would recommend reading the string of the file paths into an array. Then you will have a known number of items. Next, loop through through the array to the copy the files to the right locations.

      Let me know if that works.

  6. Ricky says:

    I am trying to use your code to apply a password to all files within a folder and its subfolders but struggling. I am using the macro below to apply a password to all files within a folder – not sure if this is the reason I am struggling!

    Sub ProtectAll()
    Dim wBk As Workbook
    Dim sFileSpec As String
    Dim sPathSpec As String
    Dim sFoundFile As String

    sPathSpec = “C:\MyPath\”
    sFileSpec = “*.xls”

    sFoundFile = Dir(sPathSpec & sFileSpec)
    Do While sFoundFile “”
    Set wBk = Workbooks.Open(sPathSpec & sFoundFile)
    With wBk
    Application.DisplayAlerts = False
    wBk.SaveAs FileName:=.FullName, _
    Password:=”swordfish”
    Application.DisplayAlerts = True
    End With
    Set wBk = Nothing
    Workbooks(sFoundFile).Close False
    sFoundFile = Dir
    Loop
    End Sub

    Any help would be greatly appreciated.

    Many thanks

    • Excel Off The Grid says:

      Hi Ricky,

      I think the only issue is that this

      Do While sFoundFile ""
      

      Should be changed to:

      Do While sFoundFile <> ""
      

      Let me know if it works.

  7. Sash says:

    Hello awesome work. I am a bit of a novice and need some help. On running my macro it asks for a unique number. Say I enter 12345 and hit enter. It then needs to find a word docx with this number in the file name. So something like *12345*.docx from a folder where the file could be located within subfolders. Can you help please?

    • Excel Off The Grid says:

      Using the DIR method above, use the following code:

      Sub loopAllSubFolderSelectStartDirectory()
      
      Dim findString As String
      findString = InputBox("Text to find:", "Find file name")
      
      'Another Macro must call LoopAllSubFolders Macro to start to procedure
      Call LoopAllSubFolders("C:\Users\marks\OneDrive\Documents", findString)
      
      
      End Sub
      
      Sub LoopAllSubFolders(ByVal folderPath As String, findString 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
      
                  If InStr(fileName, findString) > 0 Then
                      Debug.Print folderPath & fileName
                  End If
              End If
       
          End If
       
          fileName = Dir()
      
      Wend
      
      For i = 0 To numFolders - 1
      
          LoopAllSubFolders folders(i), findString
       
      Next i
      
      End Sub
      
    • Excel Off The Grid says:

      Assuming you’re using the FSO method, it would be the following:

      Before the first Sub add this code

      Dim i as Long
      

      Within the loopAllSubFolderSelectStartDirectory macro add the following code with the Dim statements

      i = 0
      

      Then instead of

      Debug.Print FSOFile.Path
      

      Use this:

      Sheets("Sheet1").Range("A1").Offset(i, 0).Value = FSOFile.Path
      i = i + 1
      
  8. Prashant says:

    Hi
    I want to check if the folder name entrred through inputbox is present in the parent folder ir not.

    C:\temp\

    Here temp is the parent folder.

    I want to search folder under temp.

    Can anyone help me out to write the vba code

  9. Michael Miles says:

    If the folder structure you are working with results in a path greater than 255 characters for your string variable VBA throws and error. Is there a method around this?

      • Michael Miles says:

        Hello, I was looping through all the files in subfolders using “Using File System Object (FSO) Late Binding – Method #2”

        After finding their folder structure was not critical and was the result of people dropping files and creating a structure haphazardly I restructured their folders and had no problem. The question still stands, if I could not restructure the folders is there a work around?

        • Excel Off The Grid says:

          Hi Michael – I can’t think of any specific reasons why this should happen. When I get a bit of time, I might look into it.

Leave a Reply

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