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

Method 1 – Loop Through Excel Files in Folder by Dir Function

The Dir function in VBA retrieves 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]])

The pathname is a string that specifies the file or folder to search for, and attributes are optional integer values that identify the file type to search for. In the following example, we will use the Dir function in the code to see the names of all Excel files in 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

In the VBA Editor, it will look like the image below.

How Does the Code Function?

folderPath = "D:\Onedrive\Desktop\"

It is the folder address 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 selected folder.

Do While filename <> ""

It means “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 selected 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 the Dir function again with no arguments returns the name of the following file that matches the pattern in the specified folder.

Execution of Code and Output Viewing

Run the code in VBA and find the output as shown in the image below. We have 3 Excel files at the mentioned address, which is why we see 3 names in the Immediate Window.

Method 2 – Using File System Object for Looping Excel Files in Folder

The “File System Object” is another way of looping Excel files into 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 names 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.

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 the FileSystemObject uses and releases any associated system resources.

Set FSOFolder = Nothing

This frees up the memory the folder object uses and releases any associated system resources.

Execution of Code and Output Viewing

Run the code by clicking the “Run” button and observing the output, as shown in the image below.

Method 3 – Formatting One Worksheet in Each Excel Files Within a Folder

It is possible to write VBA code that performs some operations on all the Excel files in a folder. Color some cells in a range in “sheet1” of all worksheets in the 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 paste the code in the editor, it looks like the image below.

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 following statements 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)

It sets the values of cells A1 through J1 on the first worksheet to the string “Excel.” It also sets the interior color of cells A1 through J1 on the first worksheet to light beige (RGB values 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”, meaning any changes made to the workbook will be saved before the workbook is closed.

Execution of Code and Output Viewing

To view the output, click on the “Run” button. A file dialog box appears, prompting to select the exact folder.

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

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

You may need to know the number of Excel files in a folder and their names. 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

Here’s how the code looks in the VBA editor.

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 stores 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 array’s contents are preserved when resized.

fileListStr = fileListStr & FileList(i) & vbCrLf

This line of code concatenates 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

Run the code above using any method mentioned in the earlier section. The output of the code looks like the image below.

Method 5 – Dir Function to Loop Through Subfolders

The first example loops 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.

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 ensure that it 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,
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 represents 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 whether the file extension of “fullFilePath” is “.xlsm” or “.xls.”

Execution of Code and Output Viewing

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

How to List Files in Folders with Specific Extensions in Excel VBA

It is also possible to loop through other types of files. In the following code, we will loop through the .txt files and assign the names of .txt files to Column B in a worksheet.

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 putting the code in the Editor, it looks like the image below.

How Does the Code Function?

extension = "*.txt"

We are defining the desired file type. We assigned the value “.txt” since we want the text file.

Do While Len(fileName) > 0

Repeatedly execute a code block 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”. Dir returns an empty string if no more file names are available in the directory.

Execution of Code and Output Viewing

To view the output, we run the macro and observe it in a sheet like the one below.

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 multiple Excel files with similar data need to be processed or analyzed.

How can I loop through files in a folder using VBA?

Using VBA, you can use several methods to loop through files in a folder, such as 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 need to provide examples of implementing 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 for selecting the folder containing the Excel files 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 potential errors during the loop.
Before performing any operations on files in a folder, create a backup.

Download the Practice Workbook

Loop Through Excel Files in Folder.xlsm

Get FREE Advanced Excel Exercises with Solutions!

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

Method 1 – Loop Through Excel Files in Folder by Dir Function

Method 2 – Using File System Object for Looping Excel Files in Folder

Method 3 – Formatting One Worksheet in Each Excel Files Within a Folder

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

Method 5 – Dir Function to Loop Through Subfolders

How to List Files in Folders with Specific Extensions in Excel VBA

Frequently Asked Questions (FAQs)

Things to Remember

Leave a reply Cancel reply