Full Path Name and Doc Name in Title Bar (97SR2 or XP)

Topic

New Reply

I have both versions 97 and XP on my computer, so I’m hoping for an answr that will cover both. I found some code for MS Word that I used that would display the full path and file name on the title bar. This same code works great for Word 97 or Word XP. I love it. I tried to use it for Excel, and change all references from Word to Excel. But, it doesn’t work. I don’t get any error messages, but the path doesn’t show in the title bar. I don’t think it’s type – I used copy/paste and then edited the Word references.

So, does anyone know the code to make the full path and document name show for the active workbook in Excel?

Reply | Quote

Replies

Do you meant the application caption bar. If so the following will change the excel caption to the full pathname of the workbook that is active when it is run :

    Application.Caption = ActiveWorkbook.FullName

Andrew C

Reply | Quote

I am talking about the blue bar at the top of the screen that tells you the name of the application and document – I’ve only heard it called the Title Bar (in the books I have anyway). So, if that’s the same thing, then yes, the application caption bar. Where do I put that? In my AutoExec code?

Reply | Quote

I think the best palce for it would be the ThisWorkbook WindowActivate event. You would howver need to include it in all you workbooks, so that the title would change each time you switched workbooks. If you just work with one workbook at a time you could palce it in the Auto_Open macro. The following must go in th eThisWorkbook codepane and not in a general module.

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
    Application.Caption = ActiveWorkbook.FullName
End Sub

Or you could use

Sub Auto_Open
    Application.Caption = ActiveWorkbook.FullName
End Sub

in a general module.

Andrew C

Reply | Quote

or create an application-level event handler.

Reply | Quote

Andrew,
Please take a look at the attached spreadsheet. I tried to incorporate several posts to have Excel show the current file address in the Title Bar. My intent was/is to have the full pathname appear in the Title Bar whenever I open an Excel file, including whenever I just open Excel to “Book 1” from my Office shortcuts.
Any ideas?
Thanks,
Jeff

Reply | Quote

Jeff,

I’m not Andrew, but what is the intention of the attached workbook? When I open it in Excel 97, I see three empty worksheets. There are no macros.

Regards,
Hans

Reply | Quote

Hans,
Thanks for your reply. This has happened to me before-I have a macro in 2000 and when I send a file via Woody’s, no macros show up. If you would e-mail me your address, I could try via “private” e-mail, and see if the macros remain.
Thanks,
Jeff

Reply | Quote

You can paste the code into a post, or paste it into a text file and attach the text file to a post. That way other Loungers can examine it too.

Reply | Quote

Andrew/Hans,
Here is the code:

Option Explicit

Sub Opener()
Dim vFileList As Variant
Dim iCount As Integer
vFileList = Application.GetOpenFilename(MultiSelect:=True)
If TypeName(vFileList) = “Boolean” Then Exit Sub

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

End Sub

Again, what I want to have occur is that every file (even the “Book 1” default file that comes up when you start Excel) that I open will have the full pathname in the Tool Bar, without the necessity of running the macro.

BTW, why won’t the macros show up when I attach a worksheet to a Woody’s post?

Thanks,

Jeff

Reply | Quote

I read, in this Lounge as I recall, that under the Customize Toolbars Command set the “Web” set of commands has an Address “thingy” that you can drag onto your toolbar and it will show your file address plus other recently visited locations. I implemented this tip and find it useful. I parked it next to the top menubar so there was enough space to display the full path.

Reply | Quote

Yes, I saw that too. But, I want it to be displayed for each workbook automatically, all the time. It works so well in Word. I was just hoping I could get it to work in Excel.

Here’s the code I got that woks for Word. In a Class Module I named AppEvents:

Option Explicit
‘ Declare a global object to represent the
‘ Word Application object for accessing Application events
Public WithEvents WordApp As Word.Application

Private Sub Class_Initialize()

End Sub

Private Sub WordApp_DocumentChange()
‘ The procedure that will automatically run when
‘ the DocumentChange event is triggered
Call MyModule.WindowTitleWithPath
End Sub

Then, in a new module

Option Explicit
‘ Set up an instance of AppEvents ready to access Application’s events
Public cAppEvents As New AppEvents

Public Sub AutoExec()
‘ Automatically runs when Word starts
‘ Initialize the WordApp object in AppEvents class
‘ Setting it to equal the Word.Application object
Set cAppEvents.WordApp = Word.Application
End Sub

Public Sub WindowTitleWithPath()
‘ Changes window title to include path with filename
‘ Check if any child windows open (avoid error if no active window)
If Windows.Count > 0 Then
‘ Change the window’s caption
ActiveWindow.Caption = ActiveDocument.FullName
End If
End Sub

Public Sub FileSave()
‘ Replaces the built-in FileSave command
‘ Check if path is empty string
If ActiveDocument.Path = “” Then
‘ If new document is not saved yet, call FileSaveAs
Call FileSaveAs
Else
‘ Existing document is already on disk; just save it
ActiveDocument.Save
End If
End Sub

Public Sub FileSaveAs()
‘ Replaces built-in FileSaveAs command
‘ Use the Show method for the built-in FileSaveAs dialog box
Dialogs(wdDialogFileSaveAs).Show
‘ Now run the code to change the window title
Call WindowTitleWithPath
End Sub

I copied/pasted this for Excel, and changed all references from Word to Excel. But, it doesn’t work.

Reply | Quote

Hi,
Did you change all the ActiveDocument occurrences to ActiveWorkbook and use something like ExcelApp.Caption rather than ActiveWindow.Caption?

Reply | Quote

Duh.. no (she says in a wee little voice). How obvious! Thanks….Will do that and let you know.

Reply | Quote

Ok. I did as you suggested, Rory. But, I’m still missing something — it’s not working. I am learning a lot from this experience, though. I am almost totally VB illiterate, but this has caused me to really examine every line. Unfortunately, I’m still not seeing the problem.

Option Explicit
‘ Set up an instance of AppEvents ready to access Application’s events
Public cAppEvents As New AppEvents

Public Sub AutoExec()
‘ Automatically runs when Excel starts
‘ Initialize the ExcelApp object in AppEvents class
‘ Setting it to equal the Excel.Application object
Set cAppEvents.ExcelApp = Excel.Application
End Sub

Public Sub WindowTitleWithPath()
‘ Changes window title to include path with filename
‘ Check if any child windows open (avoid error if no active window)
If Windows.Count > 0 Then
‘ Change the window’s caption
ExcelApp.Caption = ActiveWorkbook.FullName
End If
End Sub

Public Sub FileSave()
‘ Replaces the built-in FileSave command
‘ Check if path is empty string
If ActiveWorkbook.Path = “” Then
‘ If new document is not saved yet, call FileSaveAs
Call FileSaveAs
Else
‘ Existing document is already on disk; just save it
ActiveWorkbook.Save
End If
End Sub

Public Sub FileSaveAs()
‘ Replaces built-in FileSaveAs command
‘ Use the Show method for the built-in FileSaveAs dialog box
Dialogs(wdDialogFileSaveAs).Show
‘ Now run the code to change the window title
Call WindowTitleWithPath
End Sub

Public Sub ApplicationCaption()
‘Changes application caption to display full path and name
Application.Caption = ActiveWorkbook.FullName
End Sub
End Sub

Reply | Quote

To trap Application Workbook Activate/Deactivate events in Excel, and to do what you want just requires a class module with the following code :

Public WithEvents xlApp As Application
    
Private Sub xlApp_WorkbookActivate(ByVal Wb As Workbook)
    Application.Caption = Application.Name & "   " & Wb.Path
End Sub
    
Private Sub xlApp_WorkbookDeactivate(ByVal Wb As Workbook)
    Application.Caption = Application.Name
End Sub

To kick off that code you need to create an instance of the class, and the best place for that is in the WorkBook Open event. To terminate the class you can use the Workbook Close event. So to achieve all this place the following code in the ThisWorkBook object

Dim AppCls As New xlAppClass
    
Private Sub Workbook_Open()
    Set AppCls.xlApp = Application
End Sub
    
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set AppCls.xlApp = Nothing
End Sub

The above code can be incorporated into your personal.xls file so that it is always available, or you can place the workbook containing the code in your XLStart folder (You can have it hidden). Or just open the workbook whenever you want user the feature. As soon as the workbook is opened your caption should change to include the path of the active workbook. I just append the path without the file name as excel does include the Active Window title (i.e. the file name) in the application caption when that window is maximised, and so using the full name would duplicate the filename. With a little more effort I suppose you could work around that by using the WindowResize event.

I have not fully tested it and there might be circumstances where it might not work as expected. But I hope it is enough to get you stared. I attach a Workbook with the above code in place, so you could try it out by just downloading and opening it.

Andrew C

Reply | Quote

Here is the code that works for XL.

In Class module called AppEvents:

Option Explicit
‘ Declare a global object to represent the
‘ Excel Application object for accessing Application events
Public WithEvents ExcelApp As Excel.Application

Private Sub ExcelApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Call WindowTitleWithPath
End Sub

In a normal module:

Option Explicit
‘ Set up an instance of AppEvents ready to access Application’s events
Public cAppEvents As New AppEvents

Public Sub Auto_open()
‘ Automatically runs when Excel starts
‘ Initialize the ExcelApp object in AppEvents class
‘ Setting it to equal the Excel.Application object
Set cAppEvents.ExcelApp = Excel.Application
End Sub

Public Sub WindowTitleWithPath()
‘ Changes window title to include path with filename
‘ Check if any child windows open (avoid error if no active window)
If Windows.Count > 0 Then
‘ Change the window’s caption
Application.Caption = ActiveWorkbook.FullName
End If
End Sub

Reply | Quote

Thanks, but I am confused. What do you mean when you say “Class Module” and “Normal Module”

Reply | Quote

Does the article at http://support.microsoft.com/default.aspx?…;EN-US;Q213566&[/url] help?

StuartR

Reply | Quote

Jeff,

All this stuff with application level events, class modules etc. may be a bit too much. There is a simple alternative I use that doesn’t involve any coding at all – it displays the path and file name of the active workbook in a box in the menu bar (or in another toolbar). See this tip by Phil Rabichow. It is in the Word Forum, but it works for Excel (and PowerPoint) too.

Regards,
Hans

Reply | Quote

Hi Hans,

Nice one, that!

Reply | Quote

Jan,
I did exactly as you said, but got the following error when I reopened Excel: “Compile Error: Invalid Use Of New Keyword”, and the code “Public cAppEvents As New AppEvents” is highlighted in the normal module.
HELP!!!!!

Reply | Quote

Once you’ve created the new class module, rename it to “AppEvents” (without the quotes):

– highlight the class module in the project explorer
– make sure the properties window is visible
– click next to the Name box and change it to the above.

Reply | Quote

Again, I did exactly as you said (i. e., renaming), and got the error message “Name conflicts with existing module, project or object library”. Ay ideas?

Reply | Quote

Seems there are two class modules with the same name (albeit in different projects=workbooks). Try if using a different name (for the class module *and* in your code) helps.

Reply | Quote

Jan-I don’t understand your suggestion.

Reply | Quote

From my first message:

In Class module called AppEvents:

Option Explicit
‘ Declare a global object to represent the
‘ Excel Application object for accessing Application events
Public WithEvents ExcelApp As Excel.Application

Private Sub ExcelApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Call WindowTitleWithPath
End Sub

In a normal module:

Option Explicit
‘ Set up an instance of AppEvents ready to access Application’s events
Public cAppEvents As New AppEvents

Change the above to:

In Class module called AppTitleBar:

Option Explicit
‘ Declare a global object to represent the
‘ Excel Application object for accessing Application events
Public WithEvents ExcelApp As Excel.Application

Private Sub ExcelApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Call WindowTitleWithPath
End Sub

In a normal module:

Option Explicit
‘ Set up an instance of AppTitleBar ready to access Application’s events
Public cAppEvents As New AppTitleBar

Note I simply replaced AppEvents with AppTitleBar everywhere in the code *and* in the name of the classmodule.

Clearer?

Reply | Quote

Jan,
We must not be on the same wave-length; I did exactly as you directed. Now, I don’t get any error messages, but the window that should display the full pathname doesn’t show up even when I open a new file!
Ideas?
Jeff

Reply | Quote

Now it appears to be working fine-thanks for all of your help and patience!

Reply | Quote

Pffffeeeewwwww. Glad this is sorted.

Reply | Quote

Not so fast, my friend (LOL). One small glitch: when the pathname is displayed, there is a hyphen at the end, and the filename (only) is repeated again. For example, assume I have an Excel file on my C drive titled “Example”. When I open it up, the title bar would display “C://Example – Example”. Any way to get rid of the hyphen and second filename? In the alternative, anyway to replace the hyphen and second filename with a period and xls extension?
Thanks,
Jeff

Reply | Quote

This happens when you have the workbook in maximized state, the caption of the workbook window is then added to the application caption. So we have to check for the windowstate of the active window and act accordingly:

Public Sub WindowTitleWithPath()
‘ Changes window title to include path with filename
‘ Check if any child windows open (avoid error if no active window)
If Windows.Count > 0 Then
‘ Change the window’s caption
Application.Caption = ActiveWorkbook.FullName
If Activewindow.Windowstate=xlmaximized Then
Activewindow.Caption=” ”
Else
Activewindow.Caption=ActiveWorkbook.FullName
End If
End If
End Sub

Reply | Quote

PERFECT!!!!!

Reply | Quote

It sounds like you have sorted this problem out and, somehow, made the Excel filename and full path appear in the blue titlebar.

For those of us not VBA literate , how do I get this code to work? What code (exact words) do I put in PERSONAL.XLS and how/where do I put the code to make the filename and full path appear automatically in the blue titlebar? I need the “cookbook” directions… step 1, step 2, etc….

A copy of your code appears below:
In Class module called AppTitleBar:

Option Explicit
‘ Declare a global object to represent the
‘ Excel Application object for accessing Application events
Public WithEvents ExcelApp As Excel.Application

Private Sub ExcelApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Call WindowTitleWithPath
End Sub

In a normal module:

Option Explicit
‘ Set up an instance of AppTitleBar ready to access Application’s events
Public cAppEvents As New AppTitleBar

Thanks in advance for your guidance and patience,
Georgette

Reply | Quote

OK. Here is the step-by-step approach.
1. Open the VBE (alt-F11)
2. Make sure the project explorer is visible (otherwise, find it in the View menu)
3. Click on the project called Personal.xls
4. From the menu: Insert, Class module
5. Click on the class module just created in the explorer and make sure the properties window is visible (view menu)
6. Clcik in the box next to (Name) and change it’s content to “AppEvents” (without the quotes)
7 Into this module, paste:

Option Explicit
‘ Declare a global object to represent the
‘ Excel Application object for accessing Application events
Public WithEvents ExcelApp As Excel.Application

Private Sub ExcelApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Call WindowTitleWithPath
End Sub

Now Insert a normal module (insert, module):
and paste this into the window.

Option Explicit
‘ Set up an instance of AppEvents ready to access Application’s events
Public cAppEvents As New AppEvents

Public Sub Auto_open()
‘ Automatically runs when Excel starts
‘ Initialize the ExcelApp object in AppEvents class
‘ Setting it to equal the Excel.Application object
Set cAppEvents.ExcelApp = Excel.Application
End Sub
Public Sub WindowTitleWithPath()
‘ Changes window title to include path with filename
‘ Check if any child windows open (avoid error if no active window)
If Windows.Count > 0 Then
‘ Change the window’s caption
Application.Caption = ActiveWorkbook.FullName
If Activewindow.Windowstate=xlmaximized Then
Activewindow.Caption=” ”
Else
Activewindow.Caption=ActiveWorkbook.FullName
End If
End If
End Sub

Finally, save personal.xls (close Excel and answer yes to save it)

Reply | Quote

I’ve just put this macro into my personal.xls, and it works great Just one small (tiny?) problem. It seems to clobber the list of window names on the Window menu – it still shows me that there are (say) 3 windows open, and that window 2 is current (entry has a tick mark), but the names are blank. I’ve often got 5 or 6 linked workbooks open, and use the windows list extensively to jump from one to another.

Could you please have a look at this one more time. Thanks.
I’m using Excel 2000 SR-1

Reply | Quote

Willem,

Jeff Kirk had the same problem in post 169933. Perhaps the modified macro posted by Jan Karel Pieterse in post 169966 will help you too. If not, you might consider using the Address box from the Web toolbar. It is mentioned (with a link) in an earlier reply in this thread.

Regards,
Hans

Reply | Quote

Thanks. That did the trick.

Reply | Quote