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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
If you open a workbook and notice that Excel displays two windows for it, this has to do with how the workbook was saved. ...
Discover MoreBefore you have your macro open and read a file from disk, you'll want to check to make sure it is really there. Here's ...
Discover MoreWhen you choose to save worksheet data in CSV format, Excel gives you three choices for file formats. Those choices are ...
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