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
- Using the File System Object (FSO)
- Looping through all the files in a folder
- Using the Dir Function – Method #1
- Using the File System Object (FSO) Late Binding – Method #2
- Using File System Object (FSO) Early Binding – Method #3
- Looping through all the files in subfolders
- Using the DIR function – Method #1
- Using File System Object (FSO) Late Binding – Method #2
- Using File System Object (FSO) Early Binding – Method #3
- Conclusion
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…
From the References – VBAProject window select the Microsoft Scripting Runtime option, then click OK.
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
- Looping through all files in a folder
- 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
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.
Thank you very much for these treasures
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.
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?!
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)
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?
Hi Nick – Thanks for your question, I’ve e-mailed you directly. Hopefully we can work out the issue.
Hi Mark,
I guess you should delete the optional argument here.
Hi Frank, Yes, you’re right. I was also able to clean up a few other parts of the code too. Thank you.
Hi, code works fine, I’ve the request to filter .xlsx files, how can achieve this?
Thanks.
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.
Thanks, it works perfectly.
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?
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
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.
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
This works too: If fileName Like “*xlsx*”) Then
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.
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.
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.
Thanks Yogi, I appreciate the support.
I’m glad that I can help you and others to learn VBA.
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!
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.
Thanks Mark, I’ll try.
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
Hi Ricky,
I think the only issue is that this
Should be changed to:
Let me know if it works.
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?
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 SubSub 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 SubInstead of printing to the immediate window, how could I output to cells on a worksheet?
Assuming you’re using the FSO method, it would be the following:
Before the first Sub add this code
Within the loopAllSubFolderSelectStartDirectory macro add the following code with the Dim statements
Then instead of
Use this:
Sheets("Sheet1").Range("A1").Offset(i, 0).Value = FSOFile.Path i = i + 1I’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
Hi Ben – My guess is that you’ve included the following within the macro:
This variable should be declared before any Subs to ensure it is visible to all macros within the module.
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.
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
The “Check if a folder exists” sections of this post should help: https://exceloffthegrid.com/vba-code-create-delete-manage-folders/
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?
Hi Michael – which code snippet on this page are you referring to?
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?
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.
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.
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
Hi Pavan – The macro is running in alphabetical order. To get it to run in the order you want, you will need to change to H1 and H2 to H01 and H02.
Thanks a lot, its working.
Any other way to run a macro in sequential order for the excel files placed in folder.
A full file path is just a text string. So you would need to split and manipulate the strings to work out where the number is, then execute it in number order.
Here is a useful article which should help as a start point:
https://excelmacromastery.com/vba-string-functions/
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
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.
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.
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
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.?
Using the FileDateTime function will return the last modified date.
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.
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.
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
I think the answer you’re looking for is in this post:
https://exceloffthegrid.com/vba-code-create-delete-manage-folders/
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!!
OneDrive does cause issues as while the file path looks like a local address, it’s often a mask of a https URL.
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.
Hi Rebecca – you could use the HYPERLINK function in the next column. The syntax is:
=HYPERLINK(URL,text_to_display)
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
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.
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.
You can use
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/getextensionname-method
to get the file extension name
Can someone explain the pros and cons of these three methods?
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
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.
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.
Thank you, Mark, for your useful and clear code. It works perfectly.