This VBA “loop through files in a folder” macro quickly lets you perform operations on all files of a certain type in a given folder. It accepts two arguments, one of which is optional:
- The directory you want to loop through
- An optional filter for things like a file extension. This argument accepts an * as a wild card!
Use it anytime you need to check each file in a folder or when you want to list the files in a folder.
Macro to Loop Through Files in a Folder
Private Sub LoopThroughFilesInFolder(strDir As String, Optional strType As String) 'DEVELOPER: Ryan Wells (wellsr.com) 'DESCRIPTION: This macro finds and loops through all the files in a folder 'INPUT: Pass the procedure a string with your directory path and an optional ' file extension with the * wildcard 'EXAMPLES: Call LoopThroughFilesInFolder("C:\Users\Ryan\Documents\") ' Call LoopThroughFilesInFolder("C:\Users\Ryan\Documents\", "*txt") Dim file As Variant If Right(strDir, 1) <> "\" Then strDir = strDir & "\" file = Dir(strDir & strType) While (file <> "") 'Do what you want with the file here ' -The file name is stored as the variable "file" ' -The directory + file name can be retrieved with "strDir & file" Debug.Print file 'do not change below this line file = Dir Wend End Sub
To help you out when we explain this, make sure you have a copy of our VBA Reference Guides in front of you. We made them so you can refer to them when learning VBA and writing your own macros.
How to use the LoopThroughFilesInFolder Macro
A VBA Code Library tutorial wouldn’t be complete without a few examples of how to call the macro
Loop through txt files in Folder
Sub Demo() Call LoopThroughFilesInFolder("C:\Users\Ryan\Documents\", "*txt") End Sub
Loop through Excel files in Folder
Sub Demo2() Call LoopThroughFilesInFolder("C:\Users\Ryan\Documents\", "*.xls*") End Sub
Loop through All Files
Sub Demo3() Call LoopThroughFilesInFolder("C:\Users\Ryan\Documents\") End Sub
Loop through Files with "report" in the Filename
Sub Demo4() Call LoopThroughFilesInFolder("C:\Users\Ryan\Documents\", "*report*") End Sub
By default, the VBA macro loops through and lists the name of all files in your folder in the “immediate window”. You can remove the
Debug.Print file line and replace it with whatever you’d like.
I’ve used the macro to open each text file in a folder and convert them to Word documents. You can do the same!
I hope you enjoyed this VBA Code Library example! Use it the next time you need to use VBA to do a specific action for each file in a folder. When you’re ready to take your VBA to the next level, subscribe using the form below.
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I developed this unique 3-part free Excel training program to help you quickly learn VBA in a natural setting: right inside Excel. I'm also going to give you my entire personal macro library for free.