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