VBA Rename File: 5 code examples

VBA allows us access to the windows environment to rename a file. In this post, we will look at 5 examples of renaming files with VBA.

Rename a File VBA

To rename a file with VBA we use the Name command.

Example 1: Renaming a file

This example renames a file from Example File.xlsx to Example File Renamed.xlsx.

Sub VBARenameFile()

Name "C:\Users\marks\Documents\Example File.xlsx" As _
    "C:\Users\marks\Documents\Example File Renamed.xlsx"

End Sub

Example 2: Rename a file based on cell values

In this example, we rename a file based on cell values. The screenshot below shows the current file name in Cell C2 and the new file name in Cell C4.

We can run the following macro to rename a file using these cell values.

Sub VBARenameFileSheetNames()

Name ActiveSheet.Range("C2") As _
    ActiveSheet.Range("C4")

End Sub

Example 3: Move a file with the Name command

Did you notice the Name command requires the file path and file name? Therefore, this command doesn’t just rename but can also move files. For example, the code below will move the file from C:\Users\marks\Documents\ to C:\Users\marks\, but the file name remains the same.

Sub VBAMoveFile()

Name "C:\Users\marks\Documents\Example File.xlsx" As _
    "C:\Users\marks\Example File.xlsx"

End Sub

Example 4: Avoiding errors when renaming files

Moving files can trigger errors; these are detailed in the section below. To avoid going through the Visual Basic error debugging process, we can display a message box with an OK button.

Sub VBAAdvancedRenameFile()

'Create variables to hold file names
Dim filePath As String
Dim newFilePath As String

filePath = "C:\Users\marks\Documents\Example File.xlsx"
newFilePath = "C:\Users\marks\Documents\Example File Renamed.xlsx"

'Ignore errors
On Error Resume Next

'Rename file
Name filePath As newFilePath

'Display message if error occured
If Err.Number <> 0 Then
    MsgBox Prompt:="Unable to rename file", Buttons:=vbOK, _
        Title:="Rename file error"
End If

'Turn error checking back on
On Error GoTo 0

End Sub

Example 5: Reusable function

Finally, let’s create a reusable function. The VBA function below accepts two arguments the existing file path and the new file path.

Function fxRenameFile(filePath As String, newFilePath As String)

'Ignore errors
On Error Resume Next

'Rename file
Name filePath As newFilePath

'Display message if error occured
If Err.Number <> 0 Then
    fxRenameFile = False
Else
    fxRenameFile = True
End If

'Turn error checking back on
On Error GoTo 0

End Function

We can use this function in two ways.

1. Calling the function from another macro
2. Calling the function from a worksheet

Let’s look at both of these in turn.

Calling the function from a macro

The macro below calls the function and displays a message box

• True = File renamed
• False = Error occurred.

Sub CallFunction()

'Create variables to hold file names
Dim filePath As String
Dim newFilePath As String

filePath = "C:\Users\marks\Documents\Example File.xlsx"
newFilePath = "C:\Users\marks\Documents\Example File Renamed.xlsx"

'True = File Renamed
'False = Error Occured
MsgBox fxRenameFile(filePath, newFilePath)

End Sub

Calling the function from a worksheet

Alternatively, we can call the function just like a normal worksheet function.

Look at the screenshot above, our custom function is used in Cell C6:

=fxRenameFile(C2,C4)

TRUE indicates that the file named in Cell C2 has been successfully renamed to the file named in Cell C4. If we run the function a second time, it will show FALSE, as the file has already been renamed.

Be aware the function executes each time cells C2 or C4 change, so be careful with the order in which you update the cells.

---

---

Possible errors

If we try to rename a file or folder path that does not exist, it triggers an error: Run-time error’53’: File not found.

If the new file name is the same as an existing one, it triggers the following error: Run-time error ’58’: File already exists.

Notes on renaming files

We have used Excel workbooks in the examples, but we can use any file type. Also, we are not restricted to files; we can also rename folders using the Name command.

The Name command is core Visual Basic code. Therefore, it exists in other applications supporting VBA, such as Word and PowerPoint.

For more VBA code examples of managing files, check out this post: VBA code to copy, move, delete and manage files.

Also, check out our VBA code library for hundreds of reusable code snippets.

---

Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

---

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts: