Access Excel VBA rename workbook or file

This Access Excel VBA tutorial explains how to rename workbook or file in VBA using Name As.

Access Excel VBA rename workbook or file

It is very easy to rename a workbook or file in VBA using the below syntax. Name and As are the keywords.

Name currentFilePath As newFilePath

For example, suppose we have a file called test.xlsx in Desktop, and we want to rename it as test2.xlsx, run the below Procedure

Public Sub renameWorkbook()
    Name "C:\Users\WYMAN\Desktop\test.xlsx" As "C:\Users\WYMAN\Desktop\test2.xlsx"
End Sub

Name As does not just work on workbook, you can rename any file types.

Note that the file name can be changed, but the file path of the current file and new file must be the same; otherwise it is a copy file, not rename file.

Rename current workbook

You cannot rename a current workbook, otherwise you will receive an error below.

A workaround for this is to save current workbook as another name using Workbook.SaveAs Method.

Public Sub renameCurrentWB()
    ActiveWorkbook.SaveAs "C:\Users\WYMAN\Desktop\test2.xlsm"
End Sub

Rename mass workbook or file

Suppose we want to change the file name from column B to column C, run the below Procedure.

Public Sub renameWorkbook()
    For r = 2 To Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        filePath = Range("A" & r)
        oldFileNm = Range("B" & r)
        newFileNm = Range("C" & r)
        Name filePath & oldFileNm As filePath & newFileNm
    Next r
End Sub