Excel VBA to Loop Through Files in Folder and Rename

In today’s digital age, data organization, and file management is essential for efficient workflow and productivity. Like many other programming languages, VBA is a powerful tool for data management and can be used to automate various tasks, including renaming files. In this article, we will explore how you can use Excel VBA to loop through files in a folder and rename them.

How to Launch VBA Editor in Excel

To open the VBA code editor in Excel you can utilize the keyboard shortcut. Let’s see the process.

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

Excel VBA to Loop Through Files in Folder and Rename Them: 6 Ways

In VBA language, there are multiple ways to rename your files such as the Name method, FileSystemObject method, DIR function method, and so on. The usability of those methods depends on the type of task that you want to accomplish. For example, Name and DIR function methods are both limited to renaming files that are located in the same directory whereas the FileSystemObject method can rename multiple files in different directories but demand extra knowledge to utilize the method and its properties.

1. Using DIR Function to Rename Folder

As stated previously, the DIR function can only rename files that are located in the same directory, however, its simplicity can beat the odds it has.

1.1  Files Rename by Adding Alphabet at First

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

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

Now press F5 to Run and see the output of the code as given below.

Read More: How to Use Excel VBA to Move Files

1.2  Replacing a Specific Text Within File Name

In this section, we are going to replace a specific text in the file name. Here a code has been developed to replace Softeko with ExcelDemy in our file names.

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

2. Loop Through Files in Folder to Rename Files That Meet Specific Criteria with VBA

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

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

Read More: Excel VBA: Delete Files with Wildcards 

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

In this section of our article, we will now 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.

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

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

Now, we used a late binding code in the previous method, 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 you to use early binding and declare the FSO object as a specific type (New FileSystemObject) at the beginning of the code.

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

5. Loop Through Folder to Rename Files with Date

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

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

See the return of the given code as given below.

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.

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.

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

Read More: How to Use Excel VBA to List Files in Folder

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

VBA macro also allows you to loop through folders and subfolders to check if a file exists in them. 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. Now we have developed a VBA code below by taking Dummy Directory as the file path.

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

Download Practice Workbook

You can download and practice the code file that we have used to prepare this article.

Conclusion

Here, we have learned multiple ways of using Excel VBA to loop through files in folder and rename them. As you have already understood, there are multiple ways to do the task depending on the type of work you want to accomplish. So make sure to try all those methods at least once to get habituated to each of them. However, if you have any queries, feel free to comment below and we will get back to you soon.

Related Articles

• Excel VBA to Count Files in Folder and Subfolders
• Excel VBA to List Files in Folder with Specific Extension 
• Excel VBA to List Files in Folder and Subfolders