How to Rename a file with Excel VBA?

Excel VBA rename file operation can be done using the function mentioned below.

Usually, You can find a lot of function used in VBA under the object ‘VBA’.

But, this vba rename file is one of those few functions that exists outside of ‘VBA’ object & it can be used directly as mentioned below:

  • Function Name: Name or FSO Move
  • Syntax: Name [OldName] As [NewName]

Here is a VBA code example that shows how to change the name of a file.

Excel VBA Rename file without Opening file

From any Excel workbook, press Alt F11 to view the VB editor.

Then copy paste the below code & change the example file name to a file that exists in your computer.

Sub ExcelVBARenameFileName()
    Dim sExistingFileName As String
    Dim sRenamedTo As String
    'File to be renamed
    sExistingFileName = "D:\Filename1.txt"
    'New Name for the file
    sRenamedTo = "D:\Filename2.txt"
    'VBA command to Rename file
    Name sExistingFileName As sRenamedTo
End Sub

You cannot run this code twice. Because the old file will not exist after the first run.

This code will give error if the source file is not found in the path. So, it is better to have a VBA.Dir command to check if the file exists, then perform the rename operation.

So, press F5 only once to check this code.

Excel VBA Rename File – FSO

Here is another code in VBA that rename a file, but in here it uses file system object. Overall both the code does the same job.

Sub Rename_File()
    ' Follow this step to use FSO
    ' Tools -> Add Reference to "Microsoft Scripting Runtime"
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    fso.MoveFile "D:\Filename1.txt", "D:\Filename1_Renamed.txt"
    ' Delete old file if exits
    On Error Resume Next
    Kill "D:\Filename1.txt"
    On Error GoTo 0
End Sub

You can also add a failsafe code to delete the old file in case the above code creates 2 copies of same file.

You can also enhance this vba code to move or rename a bunch of files from a folder one by one. This way it is easy to do it automatically rather than doing it one by one manually.

Error Conditions while changing File Name

Before changing a file name, You could also include a condition to check if the old file exists. Also, check if the new file name already exists.

External reference: Here is a discussion related to file rename using Excel vba.

If the new file already exists, then the renaming step will fail. In that case the use can be intimated with appropriate message.

Once the command is executed fine, You can go to the folder and verify that the old file does not exist.

Alternate Solution to Rename file in Vba

If this does not workout, You can write a code that will read the file content & write it into a new file.

Once the content is written to new file, the old file can be deleted (get file delete code here)

So, technically the old file will not exist & logically it is renamed to a different filename.

Leave a Reply