You may want to loop through files in a folder and list their filenames in a worksheet, make changes to every file in the folder, print the filenames to PDF, and so on.
In this tutorial, I will show you examples of VBA code to loop through files in a folder using the VBA Dir function and the File System Object (FSO).
The VBA Dir Function
The VBA Dir function returns the name of a file, folder, or directory that matches a specified pattern, file attribute, or a drive’s volume label.
The function scans the target folder or directory and returns the filename of the first file or folder name that matches the specified pattern, file attribute, or drive’s volume label
The syntax of the Dir function:
Dir [ (pathname, [ attributes ] ) ]
The Dir function has two optional parameters, which are described below:
| Parameter | Description |
|---|---|
| pathname | String expression specifying a file name; may include folder or directory, and drive. An empty string (“”) is returned if pathname is not found. |
| attributes | Numeric or constant expression, whose sum specifies file attributes. If omitted, it returns files that match the pathname but without attributes. |
The attributes argument of the Dir can take any of the constants or numeric expressions below:
| Constant | Value | Description |
|---|---|---|
| vbNormal | 0 | (Default) Specifies files without attributes. |
| vbReadOnly | 1 | Specifies read-only files as well as files with no attributes. |
| vbHidden | 2 | Specifies hidden files and files with no attributes. |
| vbSystem | 4 | Specifies system files and files with no attributes. |
| vbVolume | 8 | Specifies volume label; if any other attribute is specified, vbVolume is ignored. |
| vbDirectory | 16 | Specifies directories or folders in addition to files without attributes. |
| vbAlias | 64 | The specified file name is an alias. Available only on the Macintosh. |
Looping Through All Files in a Folder
Suppose you have a folder named ‘User Guides’ on the C drive with a mix of Excel, PDF, and text files:
You can use the VBA code below to loop through all the files in the folder and print their filenames to the Immediate window:
Sub LoopThroughAllFilesInFolder()
Dim filename As String
filename = Dir("C:\User Guides\")
Do While filename <> ""
Debug.Print filename
filename = Dir
Loop
End SubWhen you run the code, it loops through all the files in the target folder and prints the filenames to the Immediate window as shown below:
Explanation of the Code
The ‘Do While’ loop continues to iterate as long as the ‘filename’ variable is not empty. When the ‘filename’ variable becomes empty, the loop stops. At each iteration, the code prints the filename of the file it has looped through to the Immediate window.
After the code prints the first filename to the Immediate window, the Dir function is used again without any arguments to get the following filename in the folder.
Note: This code prints filenames to the Immediate window. However, you can write code in the loop to perform other tasks, such as opening the files, performing calculations, extracting data, etc.
Also read: VBA to Check If Folder Exists
Looping Through Files of a Specific Type in a Folder
Sometimes, you want to loop through only files of a specific type in a folder.
Let’s say you have a folder named ‘User Guides’ on the C drive with a mix of Excel, PDF, and text files:
You can use the VBA code below to loop through only Excel files, with the XLSX extension, in the folder and print their filenames to the Immediate window:
Sub LoopThroughSpecificFileTypes()
Dim filename As String
filename = Dir("C:\User Guides\*.xlsx")
Do While filename <> ""
Debug.Print filename
filename = Dir
Loop
End SubWhen you run the code, it loops through all Excel files in the target folder and prints their filenames to the Immediate window:
This code operates similarly to the one in Example #1.1. However, it utilizes the asterisk (*) wildcard character in the ‘pathname’ argument to indicate that we want to search for all files in the specified folder with the XLSX extension.
Note: This code prints filenames to the Immediate window. You can write code in the loop to open files, perform calculations, extract data, rename files, etc. For example, you can use the code below to rename all the Excel files in the target folder:
Sub LoopRenameFiles()
Dim filename As String
Dim newname As String
filename = Dir("C:\User Guides\*.xlsx")
Do While filename <> ""
newname = "Draft_" & filename
Name "C:\User Guides\" & filename As "C:\User Guides\" & newname
filename = Dir
Loop
End SubWhen you execute the code, it loops through all Excel files in the target folder and renames them:
Also read: VBA to Create Multiple Files in a Folder
The File System Object (FSO)
The File System Object (FSO) provides access to a computer’s file system. It offers a set of properties and methods that VBA can utilize to interact with folders and files on a computer.
You can turn on FSO in two ways: Early Binding or Late Binding.
FSO (Early Binding)
In Early Binding, VBA creates an instance of FSO as soon as the file opens. For this to happen, you need to reference the FSO library in the file using the steps below:
- In the VB Editor, open the Tools menu on the menu bar and choose the References option.
- On the References—VBAProject feature, scroll down the Available References list box, select the Microsoft Scripting Runtime checkbox, and click OK.
FSO (Late Binding)
In FSO Late Binding, you reference the FSO library in the code when needed.
Note: If you need to choose between Ealy and Late Binding, I recommend using Late Binding. Although it may result in slower code, it should run with fewer errors.
Using FSO (Late Binding) to Loop Through Files in a Folder
Let’s say you have a folder named ‘User Guides’ on the C drive with a mix of Excel, PDF, and text files:
You can use the VBA code below that utilizes FSO Late Binding to loop through all the files in the folder and print their filenames to the Immediate window:
Sub FSOLateLoopAllFilesInFolder()
Dim FSOLibrary As Object
Dim folderName As String
Dim FSOFolder As Object
Dim FSOFile As Object
folderName = "C:\User Guides\"
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(folderName)
For Each FSOFile In FSOFolder.Files
Debug.Print FSOFile.Name
Next
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
End SubWhen you run the code, it loops through all the files in the target folder and prints their filenames to the Immediate window:
Note: If you prefer using FSO Early Binding, turn on the FSO library as described in the section ‘FSO Ealy Binding’ in this tutorial and use the code below:
Sub FSOEarlyLoopFilesInFolder()
Dim folderName As String
Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim FSOFile As Object
folderName = "C:\User Guides\"
Set FSOLibrary = New FileSystemObject
Set FSOFolder = FSOLibrary.GetFolder(folderName)
For Each FSOFile In FSOFolder.Files
Debug.Print FSOFile.Name
Next
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
End SubIn this tutorial, I showed you examples of VBA code to loop through files in a folder using the VBA Dir function and the File System Object (FSO). I hope you found the tutorial helpful.
Other Excel VBA articles you may also like:
