How to Use Excel VBA to Loop Through Files in a Folder and Rename (6 Ways)

How to Launch VBA Editor in Excel

Vba Editor interface

You can use the keyboard shortcut to open the VBA code editor in Excel. Let’s see how.

Press Alt + F11 to open your Microsoft Visual Basic.
Press Insert > Module to open a blank module.

opening of VBA editor in Excel worksheet

Method 1 – Using the DIR Function to Rename a Folder

1.1 Files Rename by Adding Alphabet at First

vba loop through files in folder and rename by adding Alphabet at First

If you wish to sort your files by adding the alphabet in chronological order, this code sure does the trick. The code adds an alphabet character to the beginning of each file name in a specified directory.

VBA code to loop through files in folder and rename by adding Alphabet at First

Sub Adding_Alphabet_at_First()
Dim path As String, fName As String
Dim n As Long
path = "C:\Users\User\Documents\Dummy Directory"
If Right(path, 1) <> "\" Then path = path & "\"
n = 0
fName = Dir(path & "*.*")
Do While fName <> vbNullString
Name path & fName As path & String(n \ 26, "Z") & Chr(n Mod 26 + 65) & "_" & fName
fName = Dir
n = n + 1
Loop
End Sub

Code Breakdown

The macro defines two variables – path and fName. path is set to the directory where the files are located, while fName will store each file name through the directory.
If Right(path, 1) <> “\” Then path = path & “\” checks if the path ends with a “\” character. If not, it appends it to the end of the path string.
fName = Dir(path & “*.*”) uses the Dir function to retrieve the first file name in the directory and assigns it to the fName variable.
The While loop continues to execute the Name path & fName As path & String(n \ 26, “Z”) & Chr(n Mod 26 + 65) & “_” & fName until the fName variable returns null string value.
fName = Dir uses the Dir function again to retrieve the next file name in the directory, which is assigned to the fName variable.

Press F5 to Run and see the output of the code below.

Alphabet Added at First after looping through files in folder and renaming

Read More: How to Use Excel VBA to Move Files

1.2 Replacing a Specific Text Within File Name

Initial Image of Replacing a Specific Text Within the File Name

Here, a code has been developed to replace Softeko with ExcelDemy in our file names.

VBA code for Replacing a Specific Text Within the File Name by looping through files in folder and renaming

Sub Rename_SpecificText_within_Files()
Dim path As String
Dim fileName As String
Dim newFileName As String
'Change the path directory accordingly
path = "C:\Users\User\Documents\Dummy Directory"
If Right(path, 1) <> "\" Then path = path & "\"
fileName = Dir(path & "*.*")
Do While fileName <> ""
newFileName = Replace(fileName, "Softeko", "ExcelDemy")
Name path & fileName As path & newFileName
fileName = Dir
Loop
End Sub

Code Breakdown

The macro defines three variables – path, fileName,newFileName. path is set to the directory where the files are located. fileName stores the name of each file while newFileName will be used to store each file name through the directory.
If Right(path, 1) <> “\” Then path = path & “\” checks if the path ends with a “\” character. If not, it appends it to the end of the path string.
fileName= Dir(path & “*.*”) uses the DIR function to retrieve the first file name in the directory and assigns it to the fileName variable.
The While loop continues to execute the newFileName = Replace(fileName, “Softeko”, “ExcelDemy”) command to replace Softeko with Exceldemy and Name path & fileName As path & newFileName set the file path fileName to newFileName the file name until the fileName variable returns a null string value.
The Subroutine ends with End Sub.

Final Image of Replacing a Specific Text Within the File Name

Method 2 – Using Loop Through Files in a Folder to Rename Files That Meet Specific Criteria with VBA

Initial Image of Renaming Files That Meet Specific Criteria

Another way is to construct a VBA code that will loop through files in a folder and rename them that meet specific criteria. For example, we want to change the file that contains an Underscore following the first letter of the file name.

VBA code for Renaming Files That Meet Specific Criteria

Sub Renaming_file_if_meets_specific_critria()
Dim fs As Object
Dim folder As Object
Dim file As Object
Dim oldName As String
Dim newName As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set folder = fs.GetFolder("C:\Users\User\Documents\Dummy Directory")
For Each file In folder.Files
oldName = file.Name
If Mid(oldName, 2, 1) = "_" Then
newName = "new_" & oldName
Name folder.path & "\" & oldName As folder.path & "\" & newName
End If
Next file
End Sub

From the code above, it first creates a FileSystemObject and uses it to access a folder named Dummy Directory located in the path “C:\Users\User\Documents“. Then, it loops through each file in the folder and checks if the second character of the filename is an underscore(_). If it is, the code prepends the file name with new_ and renames the file with the new name. The renamed file is saved in the same folder.

Final Image of Renaming Files That Meet Specific Criteria

Method 3 – Using File System Object (FSO) Late Binding to Rename Files

Initial Image of Files Using the File System Object (FSO) Late Binding

We will rename the abovementioned files by appending NewFileName_. The code given below will rename the files by creating FileSystemObject and using it to get a reference to the folder.

VBA code for looping through files in folder and Renaming Files Using the File System Object (FSO) Late Binding

Sub RenameFiles_With_FSO_LateBinding()
'Initialize the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set the folder path and new name
path = "C:\Users\User\Documents\Dummy Directory"
'Get the folder you want to rename files in
Set objFolder = objFSO.GetFolder(path)
'Loop through each file in the folder
For Each objFile In objFolder.Files
'Create a new file name by appending the new name and extension
Set objNewFile = objFSO.GetFile(objFile.path)
objNewFile.Name = "NewFileName_" & objNewFile.Name & "." & objFSO.GetExtensionName(objFile.path)
Next
'Release the File System Object
Set objFSO = Nothing
End Sub

Code Breakdown

The code first creates a FileSystemObject and assigns it to a variable named objFSO. Then, it sets the folder path to the Dummy Directory folder located in the path “C:\Users\User\Documents“.
Next, the code uses the FSO to get a reference to the folder by calling the GetFolder method and passing in the path variable. It assigns the reference to a variable named objFolder.
The code then loops through each file in the folder by calling the Files collection of the objFolder object and renaming the new file name.
Finally, the code releases the FileSystemObject by setting it to Nothing.

Final Image of Renamed Files Using the File System Object (FSO) Late Binding

Method 4 – Using File System Object (FSO) Early Binding to Rename Files

Initial Image of Files Using the File System Object (FSO) Early Binding

In the previous method, we used late binding code, meaning that the object type is not explicitly defined in the code. This can lead to issues if there are naming conflicts or methods/properties are unavailable in different versions of the FSO. To avoid these issues, we recommend that you use early binding and declare the FSO object as a specific type (New FileSystemObject) at the beginning of the code.

VBA code for looping through files in folder and Renaming Files Using the File System Object (FSO) Early Binding

Sub RenameFiles_With_FSO_EarlyBinding()
'Set the folder path and new name
path = "C:\Users\User\Documents\Dummy Directory"
'Initialize the File System Object
Set objFSO = New FileSystemObject
'Get the folder you want to rename files in
Set objFolder = objFSO.GetFolder(path)
'Loop through each file in the folder
For Each objFile In objFolder.Files
'Create a new file name by appending the new name and extension
objFile.Name = "NewFileName_" & objFile.Name & "." & objFSO.GetExtensionName(objFile.path)
Next
'Release the File System Object
Set objFSO = Nothing
End Sub

Code Breakdown

path = “C:\Users\User\Documents\Dummy Directory” sets the folder path and new name to a variable named path
Set objFSO = New FileSystemObject initializes the File System Object by creating a new instance of the FileSystemObject class and assigning it to a variable named objFSO.
Later, we deployed For Each loop with objFile in objFolder.Files. objFile.Name = “NewFileName_” & objFile.Name & “.” & objFSO.GetExtensionName(objFile.path) renames each file by setting the Name property of the objFile object to a new name that is created by concatenating NewFileName_ with the original file name and extension.
Finally, the code releases the File System Object by setting it to Nothing and ends the code with the End Sub command.

Final Image of Renamed Files Using the File System Object (FSO) Early Binding

Method 5 – Using Loop Through Folder to Rename Files with the Date

If you wish to append the current date to your existing file name, you can use the DATE function in the code.

VBA code for Renaming File Name with Date

Sub RenameFilesWithDate()
Const folderPath As String = "C:\Users\User\Documents\Dummy Directory\"
Set fileobject = CreateObject("Scripting.FileSystemObject")
Set folder = fileobject.GetFolder(folderPath)
currentDate = Date
    newFileName = Format(currentDate, "ddd, mmm d yyyy") & "_" & _
    fileobject.GetBaseName(file.Name) & "." & _
    fileobject.GetExtensionName(file.Name)
    file.Name = newFileName
Next file
End Sub

The code above renames files in a specified folder with a new name that includes the current date in the format of “ddd, mmm d yyyy“. The macro uses the FileSystemObject to access the folder, loop through each file in the folder, and then rename each file with the new name format. This code moves renamed files to the same folder.

See the return of the code below. Final Image of Renaming File Name with Date

Method 6 – Using Cell Range to Rename Files

Most of us get in trouble renaming the file when we have a bunch of files to that we want to rename. If you have a unique name for each file that you want to rename, it brings extra hassle to put every name corresponding to every file.

List of names for Using Cell Range to Rename Files

The alternate solution could be making a list of the file names with their corresponding new filename and constructing a VBA macro to retrieve those names and renaming the file accordingly.

VBA code for Using Cell Range to Rename Files by looping through files in folder

Sub Rename_Files_Based_onTheir_List_Name()
Dim path As String
Dim Fname As String
Dim RowNum As Long
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then
path = .SelectedItems(1)
Fname = Dir(path & Application.PathSeparator & "*")
Do Until Fname = ""
RowNum = 0
On Error Resume Next
RowNum = Application.Match(Fname, Range("B:B"), 0)
If RowNum > 0 Then
Name path & Application.PathSeparator & Fname As _
path & Application.PathSeparator & Cells(RowNum, "C").Value
End If
Fname = Dir
Loop
End If
End With
End Sub

The given code renames files in a folder based on a list of new names that we have stored in a column of the Excel worksheet as above. The macro displays a file dialog box that allows you to select a folder and then retrieve a list of files in the folder. Do while loop searches for a match in the second column (column B) of the worksheet and renames the file according to the name provided in column C. The renamed files reside in the same folder.

Excel VBA to Loop Through Multiple Folders to Check If File Exists in Folder and Subfolders

An image file in subfolder of a folder

VBA macro also allows you to loop through folders and subfolders to check if a file exists. The macro uses a recursive algorithm to search through each folder and subfolder. Then, it compares the names of the files with the specified name. The picture given above depicts the PNG file located in the New Folder. It is a subfolder of the Dummy Directory. We have developed the VBA code below by using Dummy Directory as the file path.

VBA Code to Loop Through Multiple Folders to Check If File Exists in Folder and Subfolders

Sub Check_If_File_Exists()
'Set the folder path and file name to check
strPath = "C:\Users\User\Documents\Dummy Directory"
strFileName = "Softeko 6.png"
Set fso = CreateObject("Scripting.FileSystemObject")
'Check if the file exists in the main folder
If fso.FileExists(strPath & strFileName) Then
blnFileExists = True
End If
'If the file doesn't exist in the main folder, check subfolders
If Not blnFileExists Then
Set objFolder = fso.GetFolder(strPath)
For Each objSubFolder In objFolder.SubFolders
If fso.FileExists(objSubFolder.path & "\" & strFileName) Then
blnFileExists = True
Exit For
End If
Next
End If
If blnFileExists Then
MsgBox "File exists"
Else
MsgBox "File doesn't exist"
End If
Set fso = Nothing
End Sub

The VBA code checks whether the Softeko (6) file exists in a Dummy Directory folder or its subfolders. The macro first sets the folder path and the file name to check. It then uses the FileSystemObject to check if the file exists in the main folder. If the code can not find the file in the main folder, the macro loops through each subfolder in the Dummy Directory folder. To do this, we used a For Each loop to check whether it is there. If the code finds the file in any subfolders, the macro sets a boolean variable to True and exits the loop. Finally, the macro displays a message box with the file search result.