How to Loop Through Excel Files in Folder with VBA

Get FREE Advanced Excel Exercises with Solutions!

Often we need to do the same type of work in different workbooks. It becomes very tiresome doing the same thing again and again. In this article, we will see how to loop through Excel files in a folder with VBA code. We will use For loop as well as the Do While and While loops for looping. The most interesting part of the article is finding out the total number of Excel files available in a folder as well as showing their name in a MsgBox. Hopefully, you will enjoy the whole article.


Download Practice Workbook

You may download the following workbook to practice yourself.


How to Launch VBA Editor in Excel

In this section, we are going to demonstrate how to launch the VBA Editor & create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.

  • First, we go to the Developer
  • Then we will select Visual Basic.

Steps to Open VBA Editor

Then a new window will pop up. Then we are going to follow these steps,

  • First, we will select Insert.
  • Then we are going to select Module.
  • A new Module will be created.

Opening the VBA Module


Loop Through Excel Files in Folder with VBA: 5 Suitable Examples

There are a lot of ways to loop through Excel files in a folder by VBA. But in the following section, we will cover 5 examples to demonstrate this.


1. Loop Through Excel Files in Folder by Dir Function

The Dir function in VBA is used to retrieve the name of a file or folder that matches a specified pattern in a specified directory. The basic syntax for the Dir function is:

 =Dir([pathname[, attributes]])

Where, the pathname is a string that specifies the file or folder to search for, and attributes is an optional integer value that specifies the type of file to search for. In the following example, we will use the Dir function in the code and see the names of all Excel files of a specific folder in the Immediate Window. The code is given below.

Sub Excel_fileDir()
Dim filename As String
Dim folderPath As String
'Folder path where the Excel files are located
folderPath = "D:\Onedrive\Desktop\"
filename = DIR(folderPath & "*.xlsx")
'Loop through all the Excel files in the folder
Do While filename <> ""
    If Right(filename, 5) = ".xlsx" Or Right(filename, 4) = ".xls" Then
        'Print the name of Excel file in the Immediate window
        Debug.Print filename
    End If
    filename = DIR
Loop
End Sub

If we write the code in the VBA Editor, it will look like the image below.

VBA Code to Loop Through Excel Files in folder by Dir Function

🔎How Does the Code Function?

folderPath = "D:\Onedrive\Desktop\"

It is the address of the folder where our target Excel files are located. This address is assigned to the variable “folderPath”.

filename = DIR(folderPath & "*.xlsx")

The “filename” variable is assigned the name of the first Excel file that matches the specified pattern in the specified folder.

Do While filename <> ""

It means that “do the following statements while the filename is not an empty string”. The “filename” variable is initially set to the name of the first Excel file that matches the specified pattern in the specified folder, using the

Dir function.
 If Right(filename, 5) = ".xlsx" Or Right(filename, 4) = ".xls" Then

We check if “filename” is an Excel file by using the Right function to check if the last 5 characters of the file name are .xlsx or the last 4 characters of the file name are .xls.

filename = DIR

Calling Dir function again with no arguments, which returns the name of the next file that matches the pattern in the specified folder.

Execution of Code and Output Viewing

In order to view the output, we run the code in VBA and find the output like the image below. We have 3 Excel files at the mentioned address. That’s why we see 3 names in the Immediate Window.

Output of Looping through Excel files by Dir function


2. Using File System Object for Looping Excel Files in Folder

The “File System Object” is another way of looping Excel files in a folder. It is an object in VBA that allows it to work with files, folders, and drives on a computer.It is part of the “Microsoft Scripting Runtime library”, which must be referenced in VBA code to use the FSO. In the following code, you will see the method of using FSO to find the name of Excel files in a specific folder.

Sub FSO_Folder()
Dim folderName As String
Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim FSOFile As Object
folderName = "D:\OneDrive\Desktop"
'Set all the references to the FSO Library
Set FSOLibrary = New FileSystemObject
Set FSOFolder = FSOLibrary.GetFolder(folderName)
'Loop through each file in the folder
For Each FSOFile In FSOFolder.files
    'Check if the file is an Excel file
    If Right(FSOFile.Name, 5) = ".xlsx" Or Right(FSOFile.Name, 4) = ".xls" Then
            Debug.Print FSOFolder.Path & "\" & FSOFile.Name
    End If
Next
'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
End Sub

The above code in the Editor looks like the image below.

VBA Code to use FSO to Loop through Excel Files in folder

🔎How Does the Code Work?

Set FSOLibrary = New FileSystemObject

This line of the code creates a new instance of the FileSystemObject by using the “New” keyword and assigning it to the variable “FSOLibrary”.

Set FSOFolder = FSOLibrary.GetFolder(folderName)

GetFolder method of the FileSystemObject is used to get a reference to a folder specified by the “folderName” variable. The folder is assigned to the variable “FSOFolder”.

Set FSOLibrary = Nothing

This frees up the memory used by the FileSystemObject and releases any system resources associated with it.

Set FSOFolder = Nothing

This frees up the memory used by the folder object and releases any system resources associated with it.

Execution of Code and Output Viewing

We run the code by clicking the “Run” button and observe the output like the image below.

Output of Looping Excel Files in a folder by FSO


3. Formatting One Worksheet in Each Excel Files Within a Folder

It is possible to write a code in VBA that performs some operations in all the Excel files in a folder. In the following example, we will color some cells in a range in “sheet1” of all worksheets in a folder. The code to do this is given below.

Sub Excel_file_looping()
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Prompting user for folder path
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
   With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With
'When Cancel clicked
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
'Choosing Excel file
myExtension = "*.xls*"
'Target Path with Ending Extention
myFile = DIR(myPath & myExtension)
Do While myFile <> ""
  Set wb = Workbooks.Open(filename:=myPath & myFile)
  wb.Worksheets(1).Range("A1:J1").Value = "Excel"
  wb.Worksheets(1).Range("A1:J1").Interior.Color = RGB(245, 245, 220)
  wb.Close SaveChanges:=True
  myFile = DIR
Loop
'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

If you write the code in the editor, it looks like the image below.

VBA Code to Format Sheets in Excel

🔎How Does the Code Work?

Application.FileDialog(msoFileDialogFolderPicker)

Creates a dialog box for the user to select a folder by using the msoFileDialogFolderPicker argument.

With FldrPicker

Indicates that the statements that follow are applied to the object variable “FldrPicker”.

 If .Show <> -1 Then GoTo NextCode

The “<> -1” part of the statement checks if the value of “.Show” is not equal to -1, which could be interpreted as a condition where the object is not visible on the screen.

If the condition is true, the code will execute the “GoTo NextCode” statement, which redirects the program flow to a labeled line of code called “NextCode”.

Do While myFile <> ""

The loop will continue to execute as long as the value of “myFile” is not an empty string. Once the value of “myFile” becomes an empty string, the loop will terminate.

wb.Worksheets(1).Range("A1:J1").Value = "Excel"
 wb.Worksheets(1).Range("A1:J1").Interior.Color = RGB(245, 245, 220)

First, it sets the values of cells A1 through J1 on the first worksheet to the string “Excel”.Then sets the interior color of cells A1 through J1 on the first worksheet to a light beige color (RGB value 245, 245, 220).

wb.Close SaveChanges:=True

The code uses the “Close” method of the workbook object “wb” to close the workbook. The “SaveChanges” parameter is set to “True”, which means that any changes made to the workbook will be saved before the workbook is closed.

Execution of Code and Output Viewing

In order to view the output, we click on the “Run” button. A file dialogue box appears prompting us to select the exact folder.

Choosing Folder Containing Excel Files

After choosing the folder, we click “Ok”. The code runs and we see the output in all the workbooks of that folder. In all the workbooks, the “sheet1” is formatted like the image below.

Output of Formatting Excel File in a Folder


4. Store All Excel File Names in an Array and Display Them in MsgBox

Sometimes we may need to know the number of Excel files in a folder and also the name of them. In the following section, we will discuss a code that loops through all the Excel files in a folder and shows their name in a MsgBox.

Sub Loop_folder_dispalynames()
Dim strFileName As String
Dim strFolder As String: strFolder = "D:\OneDrive\Desktop\*.xlsx"
Dim FileList() As String
Dim intFoundFiles As Integer
Dim fileListStr As String
strFileName = DIR(strFolder)
Do While Len(strFileName) > 0
    ReDim Preserve FileList(intFoundFiles)
    FileList(intFoundFiles) = strFileName
    intFoundFiles = intFoundFiles + 1
    strFileName = DIR
Loop
' Concatenate the file names into a single string
For i = 0 To intFoundFiles - 1
    fileListStr = fileListStr & FileList(i) & vbCrLf
Next i
MsgBox "Found " & intFoundFiles & " files:" & vbCrLf & vbCrLf & fileListStr
End Sub

If we write the above code in VBA Editor,it looks like the image below.

Code to Display File Names in MsgBox

🔎How Does the Code Function?

Dim strFolder As String: strFolder = "D:\OneDrive\Desktop\*.xlsx"

This line declares a variable “strFolder” as a string data type and assigns it the value “D:\OneDrive\Desktop\*.xlsx”

Dim FileList() As String

Declares an empty string array called “FileList()”, which is used to store the name of Excel files.

ReDim Preserve FileList(intFoundFiles)

This line resizes the dynamic string array “FileList()” to have intFoundFiles + 1 element, where “intFoundFiles” is an integer variable that holds the number of files found during a file search. The Preserve keyword ensures that the contents of the array are preserved when it is resized.

fileListStr = fileListStr & FileList(i) & vbCrLf

This line of code is concatenating the string variable “fileListStr” with the value of the “FileList” array at index “i”, followed by a newline character.

Execution of Code and Output Viewing

We run the code above by any method mentioned in the earlier section. The output of the code looks like the image below.

Output of VBA code Displaying Excel File Names in a folder


5. Dir Function to Loop Through Subfolders

In the first example, we saw how to loop through Excel files in a folder. In the following example, we will see how to loop through subfolders using Excel VBA. The code is given below.

Sub open_subfolder_dir()
Loop_excel_SubFolders ("D:\OneDrive\Desktop\New folder")
End Sub
Private Function Loop_excel_SubFolders(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
             If LCase(Right(fullFilePath, 5)) = ".xlsm" Or LCase(Right(fullFilePath, 4)) = ".xls" Then
                    Debug.Print fullFilePath
             End If
          End If
        End If
filename = DIR()
Wend
For i = 0 To numFolders - 1
    Loop_excel_SubFolders folders(i)
Next i
End Function

The above code in the VBA Editor looks like the image below.

VBA Code to Loop Through Subfolders in Excel

🔎How Does the Code Function?

Loop_excel_SubFolders ("D:\OneDrive\Desktop\New folder")

Calls the function “Loop_excel_SubFolders” and passes the folder address as an argument.

In the private function,

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

This line of code checks if “folderPath” ends with a backslash, and if not, appends a backslash to it to ensure that “folderPath” is properly formatted as a directory path.

filename = DIR(folderPath & "*.*", vbDirectory)

This line uses the DIR function to search for the first directory that matches the pattern “*.*” in the folder indicated by folderPath, and stores the name of that directory in the filename variable.

If Left(filename, 1) <> "." Then
        fullFilePath = folderPath & filename
  • These lines of code check if the filename does not start with a period,
  • If true, it creates a complete file path by concatenating folderPath and filename and stores it in the fullFilePath variable.
If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then
             ReDim Preserve folders(0 To numFolders) As String
             folders(numFolders) = fullFilePath
             numFolders = numFolders + 1

GetAttr(fullFilePath) : This function retrieves the attributes of a file or directory indicated by the fullFilePath string.

And vbDirectory : The vbDirectory constant is used to represent the directory attribute in VBA.

(GetAttr(fullFilePath) And vbDirectory) = vbDirectory : This condition checks if the directory attribute is set in the attributes of fullFilePath.

ReDim Preserve folders(0 To numFolders) As String : This statement resizes the folders array to accommodate a new element.

folders(numFolders) = fullFilePath : This line of code assigns the value of “fullFilePath” to the element at the “numFolders index” in the folders array.

 If LCase(Right(fullFilePath, 5)) = ".xlsm" Or LCase(Right(fullFilePath, 4)) = ".xls"

This line of code checks if the file extension of “fullFilePath” is either “.xlsm” or “.xls”.

Execution of Code and Output Viewing

In the above code, we gave the address of the folder named “New folder” in the code. So in the output, we will see the name of Excel files available in the subfolders of the “New folder”. The output looks like the image below.

Output of Looping Through Subfolders


How to List Files in Folder With Specific Extension in Excel VBA

In the above examples, we worked with Excel files only. However, it is possible to loop through other types of files also. In the following example, we will loop through the .txt files and assign the names of .txt files to Column B in a worksheet. The following code performs the work properly.

Sub ListTextFilesInFolder()
Dim folderPath As String
Dim extension As String
Dim fileName As String
folderPath = "D:\OneDrive\Desktop\" ' Change this to your folder path
extension = "*.txt" ' Change this to your file extension
' Initialize the file count to 1
i = 1
' Loop through each file in the folder
fileName = Dir(folderPath & extension)
Do While Len(fileName) > 0
    'List the file name in column B of the active worksheet
    ActiveSheet.Range("B" & i + 4).Value = fileName
    'Increment the file count and move to the next file
    i = i + 1
    fileName = Dir
Loop
End Sub

After writing the code in the Editor, it looks like the image below.

Code to List .txt Files

🔎How Does the Code Function?

extension = "*.txt" 

Defining the desired file type. Here, we have assigned the value “.txt” since we want the text file.

Do While Len(fileName) > 0

Repeatedly execute a block of code until there are no more files to process.

ActiveSheet.Range("B" & i + 4).Value = fileName
  • This statement sets the value of a cell in the currently active sheet in Excel.
  • The cell being set is determined by the concatenation of the letter “B” and the value of the variable “i” plus 4. When the value of i is 1, cell B5 gets a value, and so on.
  • The value being set is the contents of the variable “fileName”.
fileName = Dir

The Dir function returns the name of a file or directory that matches a specified pattern and then assigns it to the variable “fileName”. If no more file names are available in the directory, Dir returns an empty string.

Execution of Code and Output Viewing

In order to view the output, we run the macro and observe the output in a sheet like the image below.

Output of List .txt Files in Folder

Note: We have used the code to list the “.txt” files. By modifying the code, you may utilize it to list other file types also.

Frequently Asked Questions (FAQs)

  • Why would I need to loop through Excel files in a folder using VBA?

Looping through Excel files in a folder using VBA can be useful in various scenarios, such as when you have multiple Excel files with similar data that need to be processed or analyzed.

  • What methods can I use to loop through files in a folder using VBA?

There are several methods you can use to loop through files in a folder using VBA, such as using the Dir function, the FileSystemObject object, or the FileDialog object.

  • Is it possible to filter the files I want to loop through based on certain criteria?

Yes,you can. You just need to provide examples of how to implement file filtering in your VBA code to narrow down the files that need to be processed.


Things to Remember

  • Ensure that you have a reliable method to select the folder containing the Excel files that you want to loop through.
  • Be mindful of the file types you want to loop through. Excel files can come in various formats, such as .xls, .xlsx, .xlsm, etc.
  • Include proper error handling in your code to handle any potential errors that may occur during the loop.
  • Before performing any operations on files in a folder, make sure to create a backup of your files.

Conclusion

That is the end of this article regarding loop through Excel files in folder with VBA. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exceldemy for more exciting articles on Excel.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Junaed-Ar-Rahman
Junaed-Ar-Rahman

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo