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