Rename Files Using VBA

You can use the ‘Name’ statement in VBA to rename files. The syntax of the statement is shown below:

Name oldFilePath As newFilePath

The old file path specifies the full path from the file system’s root to the target file. The new file path gives the complete path from the file system’s root to the renamed file.

In this tutorial, I will show you four examples of using the ‘Name’ statement in VBA to rename a single or multiple files. I will use Excel workbooks in the examples, but they can apply to any file type.

Example #1: Rename a File Based on Hardcoded File Paths

Suppose you have a file ‘Annual Budget.xlsx’ in a ‘Reports’ folder on C drive and want to rename it to ‘Monthly Budget.xlsx’ in the same path.

You can use the code below to rename the file:

Sub RenameFilePathsInCode()
Dim oldFilePath As String
Dim newFilePath As String
oldFilePath = "C:\Reports\Annual Budget.xlsx"
newFilePath = "C:\Reports\Monthly Budget.xlsx"
On Error GoTo ErrorHandler
Name oldFilePath As newFilePath
MsgBox "File renamed successfully!"
Exit Sub
ErrorHandler:
MsgBox "Error renaming file: " & Err.Description
End Sub

When you execute the code, it renames the target file if it exists, and displays a message box indicating the file was renamed successfully.

If an error occurs during the renaming process, the code displays a message box describing the error.

An error can occur during the renaming process if the target file does not exist, is open, or the new file path already exists.

Also read: VBA to Check If Folder Exists

Example #2: Rename a File Using Cell Values

Suppose you have a file ‘Monthly Budget.xlsx’ in the ‘Reports’ folder on C drive and want to rename it to ‘Annual Budget.xlsx’ in the same path.

You can use the steps below to achieve your objective:

  1. Enter the existing and the new filenames with the full paths in a worksheet as shown below:
rename files VBA - existing and new file name
  1. Execute the code below:
Sub RenameFileOnCellValues()
On Error GoTo ErrorHandler
Name ActiveSheet.Range("B1") As _
     ActiveSheet.Range("B2")
MsgBox "File renamed successfully!"
Exit Sub
ErrorHandler:
MsgBox "Error renaming file: " & Err.Description
End Sub

The code uses the values on the active worksheet to rename the target file if it exists and displays a message box indicating the file was renamed successfully.

If an error occurs during the renaming process, the code displays a message box describing the error.

An error can occur during the renaming process if the target file is open, does not exist, or the new file path already exists.

Also read: VBA to Loop Through Files in a Folder

Example #3: Rename a File Using a User-Defined Function (UDF)

You can call the below RENAMEFILE User-Defined Function in a subroutine or a worksheet to rename a file:

Function RENAMEFILE(oldFilePath As String, newFilePath As String)
On Error Resume Next
Name oldFilePath As newFilePath
If Err.Number <> 0 Then
    RENAMEFILE = False
Else
    RENAMEFILE = True
End If
On Error GoTo 0
End Function

The User-Defined Function attempts to rename a file and returns the Boolean value ‘True’  if renaming is successful and ‘False’ if an error occurs and it is not successful.

As explained below, you can call the function in a subroutine or a worksheet.

Call the User-Defined Function in a Subroutine

The following subroutine calls the RENAMEFILE User-Defined Function to rename  ‘Annual Budget.xlsx’ to ‘Monthly Budget.xlsx.’

Sub RenameFileCallUDF()
Dim oldFilePath As String
Dim newFilePath As String
Dim result As Boolean
oldFilePath = "C:\Reports\Annual Budget.xlsx"
newFilePath = "C:\Reports\Monthly Budget.xlsx"
result = RENAMEFILE(oldFilePath, newFilePath)
If result Then
    MsgBox "File renamed successfully!"
Else
    MsgBox "Failed to rename the file."
End If
End Sub

If the renaming process is successful, the subroutine displays a message box indicating ‘File renamed successfully!’ If the process is unsuccessful, the message box indicates ‘Failed to rename the file.’

The renaming process can be unsuccessful if the target file is open, does not exist, or the new file path already exists.

Call the User-Defined Function in a Worksheet

You can call the UDF in a worksheet just like you would other in-built functions, as shown below:

Using UDF VBA formula to rename file

In the above example, the UDF is used in cell B3 in the below formula:

=RENAMEFILE(B1,B2)

The result TRUE indicates that the file path in cell B1 has been renamed to that in cell B2 as intended.

Note that when the formula re-executes, it will show FALSE because the target file path has already been renamed.

Note: You can wrap the above formula in an IF function so it executes only when a certain condition is met. In the example below, the RENAME function is only executed if the value in cell C3 is ‘N.’

=IF(C3="N",RENAMEFILE(B1,B2),"Don't Rename")

Using the formula above, you can control how and when the function executes. All you need to do is change the value in cell C3 when you don’t want the function to execute.

Also read: VBA Delete Files in Folder

Example #4: Rename Multiple Files Using VBA

You can use the ‘Name’ statement in VBA to rename multiple files based on cell values by looping through a list of old and new file paths.

Suppose you have ‘Annual Budget.xlsx,’ ‘Employees.xlsx,’ and ‘Sales.xlsx’ in the ‘Reports’ folder on C drive and want to rename them to ‘Monthly Budget.xlsx,’ Salespeople,’ and ‘Annual Sales.xlsx’ respectively in the same path.

You can use the steps below to rename the files:

  1. Prepare a list of old and new file paths in your Excel sheet. In this case, the old file paths are listed in column A and the new file paths are listed in column B, starting from row 2 on Sheet 2 of the active workbook.
Renaming file name using VBA
  1. Execute the following VBA code:
Sub RenameMultipleFiles()
Dim ws As Worksheet
Dim oldFilePath As String
Dim newFilePath As String
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet2")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
oldFilePath = ws.Cells(i, 1).Value
newFilePath = ws.Cells(i, 2).Value
If Dir(oldFilePath) <> "" Then
    Name oldFilePath As newFilePath
Else
    MsgBox "File was not found: " & oldFilePath
End If
Next i
MsgBox "Files renamed successfully!"
End Sub

The above VBA code does the following:

  • Sets the worksheet containing the file names.
  • Finds the last row with data in column A.
  • Loops through each row, retrieving the old and new file paths from columns A and B.
  • Checks if the old file path exists.
  • Renames the file path if it exists.
  • Notifies the user if a file path is not found.
  • Displays a message box indicating the files have been renamed successfully.

In this tutorial, I have shown you four examples of renaming files using Excel VBA. I hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.