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.
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.
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 SubIf we write the code in the VBA Editor, it will look like the image below.
🔎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" ThenWe 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 = DIRCalling 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.
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 SubThe above code in the Editor looks like the image below.
🔎How Does the Code Work?
Set FSOLibrary = New FileSystemObjectThis 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 = NothingThis frees up the memory used by the FileSystemObject and releases any system resources associated with it.
Set FSOFolder = NothingThis 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.
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 SubIf you write 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 FldrPickerIndicates that the statements that follow are applied to the object variable “FldrPicker”.
If .Show <> -1 Then GoTo NextCodeThe “<> -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:=TrueThe 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.
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.
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 SubIf we write the above code in VBA Editor,it looks like the image below.
🔎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 StringDeclares 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) & vbCrLfThis 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.
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 FunctionThe 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 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 + 1GetAttr(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.
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 SubAfter writing the code in the Editor, it looks like the image below.
🔎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) > 0Repeatedly 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 = DirThe 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.
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.
Get FREE Advanced Excel Exercises with Solutions!
