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 Easy

Stop 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!


Learn More!