Display filename in Excel titlebar (Excel 2000)

Topic

New Reply

Is there any way to display a spreadsheet’s filename in the blue titlebar when the file is open? It seems that I set this up on my old system, but it is not happening since I got a new system. Cannot find the “tip”

Thanks in advance,
gg

Reply | Quote

Replies

Hi,
Does this post help?
If you want this for all files you could also use application level events (which requires more coding but I believe there have been exaples in this forum if you search for them) or if it’s for only one file you could add code to the Workbook’s Open event.
Hope that helps.

Reply | Quote

Essentially, when I open an Excel file, I want the filename displayed in the blue bar. This is an automatic in Word and PowerPoint.

I am not familiar with macros, script, etc. so I need to know where and how to get any coding to work…

Thanks,
gg

Reply | Quote

As an alternative, you can display the path+filename in a toolbar without using any code. See Phil Rabichow’s tip – it is in the Word forum, but works for Excel as well.

Reply | Quote

Phil’s trick works… without complex coding! YEAH!!!

Many thanks,
gg

Reply | Quote

Phil’s method is neat but it does take up a lot of toolbar space, whereas the “Opener” solution puts the full path and filename in the Title Bar which is usually mostly empty. The idea came from Woody’s OFFICE Watch #6.31.

Reply | Quote

I will try this…
gg

Reply | Quote

I got the background from Woody’s archive and copied the code from the Knowledgebase article (How to Add the Workbook Path to the Title Bar) into a module in my PERSONAL.XLS and saved it.
However, when a create a new spreadsheet or open an old one, only the spreadsheet name, not the full path, appear in the title bar. I am on a company LAN. Could this be the reason it is not working?

Thanks in advance,
gg

Reply | Quote

I think not, have you assigned the macro to the Open File button on the toolbar?, if so you should get the full path in the Tilte Bar as soon as you open the file. I had to modify Jan’s original macro a bit to take care of opening several files at once, the full macro is:

Sub Opener()
Dim vFileList As Variant
Dim iCount As Integer
vFileList = Application.GetOpenFilename(FileFilter:=”Excel Files (*.xls),*.xls,All Files (*.*),*.*”, filterindex:=1, MultiSelect:=True)
If TypeName(vFileList) = “Boolean” Then Exit Sub

If IsArray(vFileList) Then
For iCount = 1 To UBound(vFileList)
Workbooks.Open vFileList(iCount)
Application.ActiveWindow.Caption = ActiveWorkbook.FullName
Next

Else
Workbooks.Open vFileList
Application.ActiveWindow.Caption = ActiveWorkbook.FullName
End If

End Sub

I cannot see why you would want the full path in the Title Bar when you create a new workbook, since this would only show the path to your template. The Woody’s article had two more macros, Saver and SaveAser, which put the full path in the TB when you do a Save or a Save As; so if you do a Save As as soon as you have created a new workbook you will have the path in the TB. These macros (which should be assigned to (a) button(s) or a menu) are:

Sub Saver()
‘Checks to see if the file has been saved by looking
‘for a colon in the FullName, which includes any path.
If InStr(1, ActiveWorkbook.FullName, “:”) > 0 Then
‘File has been saved. Show the Save dialog box and
‘put path in caption.
ActiveWorkbook.Save
Application.ActiveWindow.Caption = ActiveWorkbook.FullName
Else
‘file has not been saved. Show SaveAs dialog box and
‘put path in caption.
Application.Dialogs(xlDialogSaveAs).Show
Application.ActiveWindow.Caption = ActiveWorkbook.FullName
End If
End Sub

Sub SaveAser()
Application.Dialogs(xlDialogSaveAs).Show
Application.ActiveWindow.Caption = ActiveWorkbook.FullName
End Sub

Reply | Quote

Whilst fiddling with Opener in my above post I came across a problem. When several files are selected for opening sometimes it opens all of them, othertimes it opens only one of them. There seems to be no consistency to this, except that when it opens only one file it continues to do this until another set if files is selected (and even then sometimes only one file is opened). The problem appears to be that sometimes filelist is an array othertimes it is not. Can any of you VBA experts solve this?

Reply | Quote

I am not familiar with how Excel does macros at all. I followed Woody’s directions to make sure I had a PERSONAL.xls, copied and pasted the macro from the MS Knowledgebase into a module in PERSONAL.xls, and checked the title bar to see if it worked. The filename only, not the path, displayed in the titlebar.

Re-reading the knowledgebase directions it has a few topic headings after the code…
Running the Macro Automatically When You Start Excel: To run the macro automatically when you start Excel, save the workbook with the new code in the Xlstart folder.

This what I did and assumed that I did not have to fool around with running the macros or making toolbars to run the macros. So, even though I have put the macros in PERSONAL.xls which resides in Xlstart folder, I gather I still must run them to get the full path…. TRUE or FALSE.

What do these topics in the knowledge base mean? Do I have to “hide” the PERSONAL.xls file for it to work? Do I have to assign the macros to Built-in Menus, or can I just run the macros?

Having the Macros Available the Next Time You Start Excel
To have the macros available when you start Excel, hide the workbook. To do this, click Hide on the Windows menu. When you click Exit on the File menu, click Yes if you are prompted to save changes to the hidden workbook.

Assigning the Macros to Built-in Menus
To assign these macros to the built-in commands on the File menu, follow these steps:
1. Start Excel.
2. On the Tools menu, click Customize.
3. In the Customize dialog box, click the Toolbars tab.
4. Click to select the Worksheet Menu Bar if it is not already checked.
5. On the File menu, click Open.
6. In the Customize dialog box, click the Commands tab.
7. In the Customize dialog box, click Modify Selection.
8. Click Assign Macro. In the Macro Name list, click Opener. Click OK.
9. On the File menu, click Save.
10. In the Customize dialog box, click the Commands tab.
11. In the Customize dialog box, click Modify Selection.
12. Click Assign Macro. In the Macro Name list, click Saver. Click OK.
13. On the File menu, click Save.
14. In the Customize dialog box, click Modify Selection.
15. Click Assign Macro. In the Macro Name list, click SaveAser. Click OK.
16. In the Customize dialog box, click Close.

Sorry for all of the questions. I would really like to have the path, but apparently, did not realize how many hoop I would have to jump through to make that happen!!

Thanks in advance for your patience and guidance,
gg

Reply | Quote

Hi Georgette,

The instructions in the Knowledge Base are slightly confusing.

The Opener, Saver and SaveAser macro’s won’t be run automatically. Saving them in Personal.xls in the XLStart folder merely ensures that they are available whenever Excel runs.

The full path and file name will be displayed in the title bar when you use these macros – either directly (Tools/Macro/Macros…) or from menu items or toolbar buttons if you have assigned the macros to those. So if you have assigned the Opener macro to the File/Open… menu item as described in the KB article, but not to the Open button on the Standard toolbar, you’ll get the full path if you open a worksheet using File/Open…, but not if you open a worksheet using the Open button. That’s how it is in Excel… It’s a lot easier in Word, where you can replace built-in commands by your own. Shame on Microsoft for maintaining all these incompatibilities between different Office programs version after version…

(This is why I prefer the Address button from the Web tool bar, even though it takes up space in the menu bar)

Although you are not obliged to hide Personal.xls, it’s better to do so. Personal.xls is meant as a container for macros only; you’re not supposed to do anything with the worksheet(s) in it. So you don’t need to see the worksheet. It’ll still load automatically.

HTH,
Hans

Reply | Quote