Table of Contents
To retrieve the name of a workbook using VBA, one can use the “Name” property of the workbook object. This property allows the user to access the name of the workbook and can be used in various ways to manipulate or display it. For example, the “Name” property can be assigned to a variable to be used in a message box or as part of a file path. Another way to retrieve the name of a workbook is by using the “ActiveWorkbook” object, which refers to the currently selected workbook in the VBA project. The “ActiveWorkbook” object can also be used in combination with the “Name” property to access the workbook’s name. By using these methods, the user can easily retrieve and use the name of a workbook in their VBA code.
Get Workbook Name Using VBA (With Examples)
You can use the following methods in VBA to get the name of an Excel workbook:
Method 1: Get Active Workbook Name
Function GetWorkbookName() As String GetWorkbookName = ActiveWorkbook.NameEnd Function
This function will return the name of the active workbook such as my_vba_workbook.xlsm.
Method 2: Get Active Workbook Name Without Extension
Function GetWorkbookName() As String GetWorkbookName = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)End Function
This function will return the name of the active workbook without the extension such as my_vba_workbook.
The following examples show how to use each method in practice with a currently active Excel workbook called my_vba_workbook.xlsm.
Example 1: Get Active Workbook Name
We can create the following function to get the active workbook name:
Function GetWorkbookName() As String GetWorkbookName = ActiveWorkbook.NameEnd Function
We could then type the following formula into cell A1 to get the active workbook name:
=GetWorkbookName()
The following screenshot shows how to use this formula in practice:
We can see that the formula returns my_vba_workbook.xlsm, which is the name of the currently active workbook that we’re using.
Note: The extension .xlsm represents a macro-enabled Excel workbook.
Example 2: Get Active Workbook Name Without Extension
We can create the following function to get the active workbook name without the extension:
Function GetWorkbookName() As String GetWorkbookName = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)End Function
We could then type the following formula into cell A1 to get the active workbook name:
=GetWorkbookName()
The following screenshot shows how to use this formula in practice:
We can see that the formula returns my_vba_workbook, which is the name of the currently active workbook without the .xlsm extension.
Note: By using the Left and Instr functions, we are able to extract all characters in the workbook name to the left of the period.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: