Home / Workbook / Extract Workbook Name from Full Filename

Extract Workbook Name from Full Filename

If you have a full filename reference to a workbook “c:\documents\workbook.xls” and you want to be able to reference just the Workbook name “workbook.xls” then the following macro can be used to extract the Workbook name.

This can be useful if you want to test if a Workbook is already open before attempting to open it. This is good practice and avoids receiving the standard Excel warning messages (see below) if you attempt to open a Workbook that is already open.

Extract Workbook Name

Step 1

Open a workbook, then start the Visual Basic Editor (Alt + F11) and copy the following macro into the editor

Function strExtractWorkbookName(strFullFileName As String) As String
'This function will extract the Workbook Name from the end of a Full File Name string
 
Dim i                       As Integer
Dim strChar                 As String
 
    i = 0
    strChar = Mid(strFullFileName, Len(strFullFileName) - i, 1)
    strExtractWorkbookName = ""
    Do While strChar <> "\"
        strExtractWorkbookName = strChar + strExtractWorkbookName
        i = i + 1
        strChar = Mid(strFullFileName, Len(strFullFileName) - i, 1)
    Loop
 
End Function

Step 2

Now you can use this to extract a Workbook name from a full filename. See the sample code below that extracts the Workbook name from a full filename and then based on a test that checks if the Workbook is already open it sets a Workbook object to the existing Workbook or Opens for the first time.

......
        strTemplateWorkbookName = strExtractWorkbookName(strFullTemplateFileName)
        If Not IsWbOpen(strTemplateWorkbookName) Then
            Set wbQuoteTemplateWorkbook = Application.Workbooks.Open(strFullTemplateFileName)
        Else
            Set wbQuoteTemplateWorkbook = Application.Workbooks(strTemplateWorkbookName)
        End If
.......

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk