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

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

Table of Contents

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 that 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 opens the FSO library as soon as the workbook opens. For this, we need to reference the FSO library in the file.

In the Visual Basic Editor, click Tools > References…

VBA Editor - Tools References

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

Microsoft Scripting Runtime required for FSO VBA Loop through files in folder

Late Binding

Late Binding does not require any specific actions to enable the FSO Library. We reference the library 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 at prints 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. Only the files matching the pattern set by the wildcard characters are included in the scope of the loop.

Example wildcard codes:

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

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

    '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

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)  'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFolder.Files

    '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

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 – Method #1

This example prints 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) Early 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

Conclusion

In this post, we have seen 6 methods to loop through files in a folder with VBA.

The Dir function methods are native to VBA and do not require any additional libraries. But the FSO methods require either early or late binding to ensure they run correctly.


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

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

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

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

      Reply
  1. 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?

    Reply
    • 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.

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

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

      Reply
      • 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.

        Reply
        • Hi Mark,

          Yep that did the trick! Thank you so much for responding so quickly and helping out. You sir, are the man!
          Greets, Frank

          Reply
  3. 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.

    Reply
    • 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.

      Reply
      • 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.

        Reply
  4. 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!

    Reply
    • 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.

      Reply
  5. 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

    Reply
    • 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.

      Reply
  6. 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?

    Reply
    • 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
      
      Reply
    • 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
      
      Reply
      • I’m using this one for a loop through subfolders and printing out the file name to A1 when it goes into the first folder and prints out the names its correct then when it goes into the second folder it overwrites the first list of names.

        Any help would be much appreciated!

        My folder layout is

        Folder 1
        -Sub1
        –File
        –File
        –File
        -Sub2
        –File
        –File
        –File
        -Sub3
        –File
        –File
        –File

        Reply
        • Hi Ben – My guess is that you’ve included the following within the macro:

          Dim i as Long

          This variable should be declared before any Subs to ensure it is visible to all macros within the module.

          Reply
      • Hello Excel off the grid,
        Thanks for these codes. Like for FSO method ? How could I output to cells, using the dir method ? I was trying to use the dir method to find string in the solution posted for sash, but I also wanted the output to be printed in excel sheets.

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

    Reply
  8. 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?

    Reply
      • 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?

        Reply
        • 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.

          Reply
          • Hello,

            I have the same issue. It looks like that when going through folders, files name are not all retrieved as the number of characters for both folders and file names is too long.
            is there a way round it ?

            thanks

          • This appears to be a long-standing issue with Excel, there are quite a few forum questions on similar things. Which version of Excel are you using?

            It appears the only option is to split the string into separate cells.

  9. I have created a macro which selects the each excel file placed in folder and run the code, copies the value and save. it working fine. but its not running in sequential order.

    example : I have 4 files in folder (filenames): H1, H2, H10, H11.

    macro runs in this order H1, H10, H11, H2

    but expected run for me is H1, H2, H10, H11.

    Request your help on this topic

    Reply
  10. This code is great and I am hoping to build on it to get what I need. I have been trying for some time to get a full list of content in a sub folder structure similar to what is achieved here but I also need to include:
    path to empty sub folders.
    temp files/files with no file extension.
    some way to differentiate Files from Folders (without relying on file extension).

    I assume that I need to tweak these two lines of code, but I am now at a bit of a loss. Are you able to help?

    fileName = Dir(folderPath & “*.*”, vbDirectory)
    While Len(fileName) 0

    Reply
  11. Please, I tried the macro “Using File System Object (FSO) Late Binding – Method #2” with two subs.
    However it stopped and wanted to debug the line in second sub:
    “For Each FSOSubFolder In FSOFolder.subfolders”.
    Could you help, please.

    Reply
    • I’ve re-tested the code and it appears OK. The most likely issue is that the file path is missing a “\” at the end.

      Let me know if that works.

      Reply
  12. Hello sir, I was looking for a way to run a VBA macro to all the pptx files within a specific folder’s subfolders and I’ve stumbled upon your guide which looks exactly what I need. However I’ve tried all your subfolers loop methods but to no avail . I’ve only replaced the path in the first function with mine (note that the folder contains 5 sample pptx files) and “Debug.Print FSOFile.Path” with my piece of code:
    I open a blank pptx presentation, open the vba editor, turn on the scriptining runtime and run the pasted customized code.
    Instead of creating a txt box in each file, the script creates 5 boxes (one per sample file) in the blank presentation. I wonder what I’m doing wrong.
    Any help would be hugely appreciated.

    He’s my piece of code:

    Dim shp As Shape
    Dim offset_height As Integer
    offset_height = 0

    Set shp = ActivePresentation.Slides(1).Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
    Left:=318, Top:=816 + offset_height, Width:=250, Height:=25)
    shp.TextFrame.TextRange.Text = “Rev 01/02/2020”
    shp.TextFrame.TextRange.Font.Size = 12
    shp.TextFrame.TextRange.Font.Name = “Montserrat SemiBold”
    shp.TextFrame.TextRange.Font.Bold = msoFalse
    shp.TextFrame.TextRange.Font.Color = RGB(255, 255, 255)
    shp.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignRight

    offset_height = offset_height + 30

    Reply
  13. Hi Mark,
    The post is the best out of all the other info I found on other sites.
    I need small help here along with looping through the files in folders.
    Is there a way to check the modified date of the files in the path and to the recent files I need to run the specific macro.?

    Reply
  14. I’m a bit confused. In the example using FSO LoopAllSubFolders() you are calling LoopAllSubFolders.

    I am trying to loop through files as well as subfolders that contain files.

    Reply
  15. Hi,

    This was very helpful in getting going in the right direction. I’m not to savy with VBA or any coding.

    I used the File System Object (FSO) Late Binding – Method #2 approach. My goals to be able to save each file updated with the actions as a new one. I tried this but no luck..
    myFileName = “C:\Users\Desktop\Document\Newly Structured”

    wbnew.SaveAs fileName:=myFileName & “.xlsm”

    My end goal is to update some files that all have the exact same set up just different data values and be able for them to save in a new folder. Please let me know if you have any recommendations or leads on the next steps.

    Reply
  16. Hi,

    How could I use these code to search (with partial name defined in for like A1:A5) and copy all matched files (excel file) from folder/sub-folders to specify location (ex: “D:\copyto\”)

    Regards,
    TB

    Reply
  17. Hi,

    I’m struggling with a problem on OneDrive. I have a OneDrive excel sheet open on my local PC. I have a macro which creates an invoice in pdf format and saves it to a sub-directory on my OneDrive. All ok so far. Subsequently, I need to use another macro to email the pdf invoice. Within the macro I test to verify the invoice exists but I get an error here because the macro can’t find the directory to which it previously saved the pdf!? I am using the same OneDrive url both to create the pdf and retrieve the pdf. HELP!!

    Reply
    • OneDrive does cause issues as while the file path looks like a local address, it’s often a mask of a https URL.

      Reply
  18. Your code above helps with half of my problem. I needed to get the file names and extensions to be able to dynamically create hyperlinks for each unique part number record in teh form. Your code provides the ability to cycle through and grab each record’s associated file’s URL on the our share drive, however, I do not know how to set the record field to create a hyperlink to the URL in the form.

    Any help would be greatly appreciated. I have 7000+ records and Change Orders that I have export the records from 2000 SQL Workbench and now I am trying to use access to perform the same functions so I can populate our sharepoint site dyanmically instead of writing individual html hyper in html. which would suck.ks.

    Reply
  19. Hi Mark,

    This is absolutely a bundle of treasure like others said. Your instructions are very clear and simple. It helped me to solve the last part of a puzzle – writing a program to loop through a folder where ten subfolders hold many legal cases I need to read and count the pages for each file.

    AL

    Reply
  20. Hello! These work incredibly, thanks for these treasures as others have said. One question: what’s the advantage to using the two FSO methods as opposed to the Dir() method? Are they faster or something? The Dir() method seems to work fast enough in my opinion.

    Reply
    • The FSO library provides additional functionality, which isn’t available via Dir(). But if you’re just looping through files, then I would probably go for the Dir() method too.

      I’ve not done a speed test, so I’m not sure which is the fastest. Would be interested to know if anybody else has done one.

      Reply
  21. Hi please i need change this code
    i need add subfolder to me copysource

    Sub CopyAllPic()

    Dim LastRowC As Integer
    Dim i As Long
    Dim MyFile As String, Copysource As String, Package As String, folderPath As String

    Copysource = ActiveWorkbook.Sheets(“100 paczek”).Range(“A2”).Value & “PIC94”
    LastRowC = Range(“C1”).End(xlDown).Row

    For i = 2 To LastRowC

    Package = Sheets(“100 paczek”).Cells(i, “C”) & “*.jpeg”
    MyFile = Dir(Copysource & Package) ‘ wyszukiwanie pliku w folderze po części nazwy

    Do While MyFile “” ‘ Start the loop.

    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Debug.Print MyFile ‘ Display entry only if it

    FileCopy ActiveWorkbook.Sheets(“100 paczek”).Range(“A2”).Value & MyFile, “y:\Bratislava-Warehouse\QS\Level-1\100 balikov\” & MyFile
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    MyFile = Dir ‘ Get next entry.

    Loop

    Next i

    End Sub

    Reply
  22. Hi,
    thanks for the tips.
    I am just wondering when it comes to Using the File System Object (FSO) Late Binding – Method #2
    Why loop looks like:
    For Each FSOFile In FSOFile
    ”code
    Next

    shouldn’t For Each FSOFile in FSOFolder ? Or at least
    For Each FsoFile in FSOFolder.Files ?

    Thank you.

    Reply
    • Hi Adam – it is because FSOFile is an object but also Set with a value, therefore it operates fine. But, I see your point about the code looking odd. Therefore, I have updated the examples to have clearer naming consistency. Thanks for commenting, I think that will make it easier for others to follow.

      Reply

Leave a Comment