VBA Workbook Name (Get, Set, without Extension)
This tutorial will demonstrate how to get and set the Workbook name in VBA.
We can get or set the name of the Active workbook in VBA, or loop through all the open workbooks in Excel, and get or set the name of each of them using a VBA Loop.
Get Workbook Name
To get the name of the active workbook, we need to use the name property of the workbooks object.
Sub GetWorkbookName() Dim strWBName As String strWBName = ActiveWorkbook.Name MsgBox strWBName End Sub
If we were to run the code above, we would see a message box appear on the screen with the name of the Active workbook.
To loop through all the active Workbooks, and return the names of the workbooks to Excel, we can run the following code:
Sub GetWorkbookNames() Dim wb As Workbook For Each wb In Workbooks ActiveCell = wb.Name ActiveCell.Offset(1, 0).Select Next End Sub
The examples above will include the extension of the file (eg xlsx). If you do not want to include the extension, there are a few methods we can use to obtain just the filename of the workbook.
Get Workbook Name Without Extension
We can use the LEFT and INSTR functions to remove any characters after the period in the file name:
Sub GetWorkbookName() Dim strWBName As String strWBName = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) MsgBox strWBName End Sub
We can use the LEFT and LEN functions to remove 5 characters from the end of the file name:
Sub GetWorkbookName() Dim strWBName As String strWBName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 55) MsgBox strWBName End Sub
Setting the Workbook Name
To set the name of a workbook in VBA, we still use the Name property of the workbook, however we cannot use this method to change the name of the Active Workbook. This is due to the fact that the Active workbook is open, and a file access error will occur. To overcome this, we can save the file with a new name and then delete the old file.
Public Sub SetWorkbookName() Dim strPath As String Dim strNewName As String Dim strOldName As String strOldName = ActiveWorkbook.Name strNewName = InputBox("Please enter new name for workbook") strPath = ActiveWorkbook.Path ActiveWorkbook.SaveAs strPath & "/" & strNewName Kill strPath & "/" & strOldName End Sub
To rename a workbook that is not open, we can use the Name method.
Public Sub RenameWorkbook() Name "C:\Data\MyFile.xlsx" As "C:\Data\MyNewFile.xlsx" End Sub
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!