Return to VBA Code Examples

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.

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:

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:

We can use the LEFT and LEN functions to remove 5 characters from the end of the file name:

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.

To rename a workbook that is not open, we can use the Name method.

 

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! vba save as


Learn More!