How to 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.Name
End 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.Name
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.xlsm, which is the name of the currently active workbook that we’re using.

Note: The extension .xlsm represents a macro-enabled Excel workbook.

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.

The following tutorials explain how to perform other common tasks in VBA: