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:

  1. The directory you want to loop through
  2. 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

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 Demo()
Call LoopThroughFilesInFolder("C:\Users\Ryan\Documents\", "*.xls*")
End Sub

Loop through All Files

Sub Demo()
Call LoopThroughFilesInFolder("C:\Users\Ryan\Documents\")
End Sub

Loop through Files with "report" in the Filename

Sub Demo()
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.

VBA Loop Through Files in Folder

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.

Submit a comment below if you like this macro or have more questions. As always, subscribe to my email list, share this article on social media and follow me on Google+ and Twitter for more great VBA content!


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.