Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Full Path Names in Excel.
Written by Allen Wyatt (last updated July 13, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
When you open a workbook in Excel, the workbook name is displayed in the title bar. At times, it would be nice to display more than a simple workbook name in the title bar. Many people could profit by a way to display a full path name along with the workbook name in the title bar. Unfortunately, Excel does not provide a way to do this easily.
If you only need to know the full path name once in a while, then you can create a very simple macro and assign it to the QAT or a shortcut key. When you run the macro, the information in the title bar for the active window is changed to reflect the full path name. This macro, called ChangeCaption, is as follows:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Sub ChangeCaption()
ActiveWindow.Caption = ActiveWorkbook.FullName
End Sub
The only drawback to this approach is that whenever you rename your workbook by saving it under a different name, the new file name (and path) are not updated in the title bar unless you rerun the macro.
If the full path name is too long to reasonably fit in the title bar, then you might consider using a macro that displays it in a message box:
Sub GetFullName()
MsgBox ActiveWorkbook.FullName
End Sub
Or, if you'd rather put the full path name in a cell, you could use the following formula:
=CELL("filename")
The difference between the CELL worksheet function and the earlier macros is that it returns a filename that (1) includes brackets around the workbook name and (2) includes the name of the worksheet on which the function is used.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7850) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Full Path Names in Excel.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Excel tries to make sense out of any data that you import from a non-Excel file. Sometimes this can have unwanted ...
Discover MoreIf you need to stuff the current workbook's filename and path into a cell or a header or footer, you'll appreciate the ...
Discover MoreRenaming a workbook from within Excel can seem daunting, but it is actually quite easy. All you need to do is use the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-07-15 09:36:36
J. Woolley
For more on this subject, see https://excelribbon.tips.net/T011356
2024-07-14 05:44:17
Kiwerry
Thank you as ever, Allen
My preference is for the CELL function because it allows one to get the information without the use of macros, and supplies the name of the worksheet as well.
Example:
=CELL("filename",A1) results in something like D:\Data\MyPath\[MyFile.xlsx]MySheet
I have found it advisable to add the second parameter (here ,A1 ) to ensure that the correct workbook/sheet are shown.
If the full path name alone is required, the following addition will remove the square brackets and worksheet name:
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments