Introduction
“I have a list of product codes and names, but the actual image files are named with just codes…” or “I want to rename files in bulk to match a list managed in Excel.”
The need to batch rename a large number of files using data from an Excel sheet is common in many business tasks.
By using the FileSystemObject (FSO) in VBA, you can automate this work with a single click. This article introduces a safe and reliable method to batch rename files according to a list of “Current File Name” and “New File Name” created in an Excel sheet.
Preparing the Rename List
First, create a list in an Excel sheet. We will name this sheet “RenameList“.
| A (Current File Name) | B (New File Name) | C (Result) | |
| 2 | product_001.jpg | NewProductA(Summer).jpg | |
| 3 | product_002.jpg | PopularItemB(Limited).jpg | |
| 4 | product_003.jpg | StandardItemC.jpg | |
| 5 | IMG_9999.jpg | (Delete).jpg | |
| … | … | … | … |
- Column A: Enter the current name of the file you want to change.
- Column B: Enter the new name for the file.
- Column C: Leave this empty. VBA will write the result (Success, Failed, etc.) here.
What is “Two-Stage Renaming” (Safe Processing)?
If you rename files sequentially from the top of the list, you might encounter naming conflicts. For example, if you try to rename File A to “File B,” but “File B” already exists further down the list as a file to be renamed later, an error will occur.
To prevent this, we use a technique called “Two-Stage Renaming”.
- Stage 1 (Temporary Rename): First, change all original files in the list to unique “temporary names” that will definitely not duplicate.
- Stage 2 (Final Rename): Then, change the files with “temporary names” to the “new names” specified in the list.
By following this procedure, you can completely eliminate the risk of file name conflicts during processing.
VBA Code to Rename Files Based on the List
This code uses the list starting from cell A2 on the “RenameList” sheet to rename files inside a folder named “TargetFiles” (located in the same folder as the Excel file).
The Complete Code
Sub RenameFilesBasedOnList()
'== Define variables ==
Dim objFSO As Object
Dim ws As Worksheet
Dim nameList As Range
Dim targetRow As Range
Dim folderPath As String
Dim currentFilePath As String
Dim tempName As String
'== Basic Settings ==
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ws = ThisWorkbook.Worksheets("RenameList")
' Path to the folder containing the files (same location as this workbook)
folderPath = ThisWorkbook.Path & "\TargetFiles\"
' Automatically get the list range from A2 to the last row
Set nameList = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp)).Resize(, 3)
' Clear the results column (Column C)
nameList.Columns(3).ClearContents
'== Stage 1: Rename to temporary names ==
For Each targetRow In nameList.Rows
currentFilePath = folderPath & targetRow.Cells(1, 1).Value ' Current file path
If objFSO.FileExists(currentFilePath) Then
' Generate a unique temporary name using timestamp and row number
tempName = "temp_" & Format(Now, "yyyymmddhhmmss") & "_" & targetRow.Row & ".tmp"
' Execute temporary rename using FSO
objFSO.GetFile(currentFilePath).Name = tempName
' Record the temp name in Column C (Result Column)
targetRow.Cells(1, 3).Value = tempName
Else
' If the file does not exist
targetRow.Cells(1, 3).Value = "File Not Found"
End If
Next targetRow
'== Stage 2: Rename to new names ==
For Each targetRow In nameList.Rows
tempName = targetRow.Cells(1, 3).Value
' Process only if the temporary rename was successful
If tempName <> "File Not Found" Then
currentFilePath = folderPath & tempName ' Path of the temp file
' Execute final rename using FSO
objFSO.GetFile(currentFilePath).Name = targetRow.Cells(1, 2).Value ' New name from Column B
' Update result to "Success"
targetRow.Cells(1, 3).Value = "Success"
End If
Next targetRow
MsgBox "Processing complete.", vbInformation
'== Release objects ==
Set objFSO = Nothing
Set ws = Nothing
End Sub
Explanation of the Code
Set nameList = …
The code automatically detects the range of the list from cell A2 to the last row of data in Column A. This means you don’t need to modify the code even if the number of rows in your list changes.
Stage 1 Loop
For Each targetRow In nameList.Rows: Processes the list one row at a time.If objFSO.FileExists(...): Checks if the file listed in Column A actually exists.tempName = ...: Combines the current time (down to the second) and the row number to create a temporary filename that will absolutely never duplicate.objFSO.GetFile(...).Name = tempName: Executes the rename to the temporary name.targetRow.Cells(1, 3).Value = tempName: Notes which file was changed to what temporary name in Column C for later use.
Stage 2 Loop
If tempName <> "File Not Found" Then: Checks the note in Column C and processes only the files that were successfully renamed temporarily.objFSO.GetFile(...).Name = targetRow.Cells(1, 2).Value: Renames the temporary file to the intended “New File Name” listed in Column B.targetRow.Cells(1, 3).Value = "Success": Updates the result in Column C so the user can easily see the outcome.
Summary
In this article, I introduced a practical method to batch rename files using FSO based on an Excel list “blueprint.”
- Management: By creating a list in Excel, even complex renaming rules are easy to manage.
- Safety: By using the “Two-Stage Renaming” technique, you can process files safely without worrying about filename conflicts.
- Visibility: By writing the results back to the Excel sheet, you can easily verify which files succeeded and which files were missing.
This method can be applied to various situations such as product management, document organization, and photo sorting. Please use it to automate your routine renaming tasks.
