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)

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)
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.