Show path on Program Title bar? 4

[gs99]

The program title bar shows "Microsoft Excel - workbookname". Is there an option within the basic Office application to include the path, so it would show C:\workbookname? Evidently there are ways to do this from within a VBA program; is that the only way?

 

[whatalulu]

I believe if you go to SUPPORT.MICROSOFT.COM you will find the article you are looking for. Search for workbook + title + path and you should find it.

Lulu

 

[cheerio]

Try putting the following code in the workbook open event:

ActiveWindow.Caption = ActiveWorkbook.FullName

Ken

 

[gs99]

The Microsoft document (Q272717) in Lulu's reply may be helpful to some - thanks. However it seems to be a VBA-only solution. I'm looking for a NON-VBA way (user option) to show the CURRENT path on the Title bar, even updated after a SaveAs event. This provides positive identification of the current workbook or document, thereby improving visibility and decreasing errors = productivity.
My original scenario that prompted the question was in a programmer / testing mode in which I'm working on C:\File. I SaveAs to A:\File (or other path for test version). There is no indication on the screen that I'm now looking at File in path A:\.
(The Workbook was protected, so File/Properties is dimmed. But why do I need to go there anyway to see basic information about a file - its path?)
Thanks

 

[DaleWatson123321]

Hi gs99,

I had the same concern, and searched through Excel's "Customize" options a couple of years ago. I use Excel '97. I believe the solution I found is the solution you're seeking.

It's accomplished by using the following steps:

1) Activate "Customize" by either"
a) Tools - Customize, or
b) Right-click on the Toolbar, and click "Customize"

2) Click on the "Commands" tab.

3) Under "Categories:" (on the left), scroll down and click on "Web".

4) Under "Commands" (on the right), click-and-drag "Address" (should be the first item at the top). Drag this to a place on one of your existing toolbars.

5) You can adjust the width of this "Address icon" by clicking on its right side and dragging.

6) If you don't have room on any existing toolbar, you can create a "new" toolbar, by:
a) Click on the "Toolbars" tab.
b) Click "New" (top-right-corner).
c) Drag your new toolbar to the top.

That's all. Now when you open a file, both the "path" and the "filename" will show in the "Address icon".

I hope this meets your expectations. Please advise as to how you make out.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[gs99]

Thanks Dale Watson. I followed your instructions in my Excel-2000 and WindowsXP. The info you provided is a workable solution for me, with these "compromises": (1)The Addresss icon and info could not be placed on the Program Title bar (which has a lot of empty space), but on a Tool Bar. (2)The address is not updated automatically after a SaveAs; but another action (such as flipping to another window and back) is needed to refresh the info.
If anybody has better results or suggestion let us know.
PS. Why do they make things so difficult / non-productive? The path is a basic and important part of the file's FULLNAME; why not make it easy to show the full name? I could not find your info in my Help. The Msoft article did not provide this non-VBA solution as an option.)
Thanks again for your help.

 

[cheerio]

The path can be placed on the menu bar to the right of Help where there is usually lots of space.

Ken

 

[mbarron]

A quick check method (I'm borrowing from Dale's solution) would be to simply to right click on the toolbar and check the "Web" toolbar.

Or create the new toolbar as Dale suggests, and turn that one on/off if you only want to check where your file resides, insteaed of leasing space from one of the other more permanent toolbars. Mike

 

[rmikesmith]

gs99,

I have put together an Excel add-in that will show a workbook's full path and filename in either the application title bar or individual workbook window title bar, depending on whether the window is maximized. This has the advantage of working invisibly, behind-the-scenes, for any open workbooks, while not requiring that anything be added to your own workbooks. When the add-in is loaded, all open workbooks will have their window captions updated to include the full path + workbook name. If a new workbook is created, upon saving, again, its full pathname will be displayed. If the add-in is unloaded, all workbook captions will be restored to workbook names only.

I am reproducing the code here for others who may be interested but if you or anyone else would like a copy of the add-in, post your email address and I'll be happy to forward it.

This add-in uses Excel application-level events and, therefore, requires creating a class to access these. The following code is placed into a Class Module:

Option Explicit

Public WithEvents AppEvents As Application


Private Sub AppEvents_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)

  Select Case Wn.WindowNumber
  Case 1
    Wn.Caption = Wb.FullName
  Case Else
    Wn.Caption = Wb.FullName & ":" & Wn.WindowNumber - 1
  End Select
  
End Sub


Private Sub AppEvents_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FName As String

  If Wb.Path <> &quot;&quot; Then Exit Sub
  Cancel = True
  FName = Application.GetSaveAsFilename(FileFilter:=&quot;Microsoft Excel Workbook,*.xls&quot;)
  If FName = &quot;False&quot; Then Exit Sub
  On Error Resume Next
  Application.EnableEvents = False
  ActiveWorkbook.SaveAs FName
  Application.EnableEvents = True
  ActiveWindow.Caption = Wb.FullName
End Sub

The next code is placed in a standard Code Module:

Option Explicit

Dim AppObject As New AppClass


Sub Init()
  Set AppObject.AppEvents = Application
End Sub

The following is placed into the ThisWorkbook code module:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Wkb As Workbook
Dim Wnd As Window

  For Each Wkb In Application.Workbooks
    For Each Wnd In Wkb.Windows
      Select Case Wnd.WindowNumber
      Case 1
        Wnd.Caption = Wkb.Name
      Case Else
        Wnd.Caption = Wkb.Name & ":" & Wnd.WindowNumber - 1
      End Select
    Next Wnd
  Next Wkb

End Sub

Private Sub Workbook_Open()
Dim Wkb As Workbook
Dim Wnd As Window
  
  Call Init
  
  For Each Wkb In Application.Workbooks
    For Each Wnd In Wkb.Windows
      Select Case Wnd.WindowNumber
      Case 1
        Wnd.Caption = Wkb.FullName
      Case Else
        Wnd.Caption = Wkb.FullName & ":" & Wnd.WindowNumber - 1
      End Select
    Next Wnd
  Next Wkb
  
End Sub

Notes:
1) I used the WindowActivate event rather than the WorkbookActivate event to properly handle opening multiple windows for a single workbook (i.e., clicking Window/New Window from the main menu).
2) Because there is no AfterSave event handler , I had to use the BeforeSave handler and duplicate Excel's Save As functionality to capture when the workbook was assigned a name and path.


Regards,
M. Smith

 

[PeterMoran]

Mike,

Sounds a great solution.
A copy to moranpeter(at)hotmail.com when you have time.

Many thanks,

Peter Moran

 

[PeterMoran]

Mike,

Had the Addin in and going - its great! Many Thanks.

However I have a spreadsheet that is normally loaded with the second sheet open, the first sheet is just a header, and the second is the work sheet. When I open this workbook with the Addin installed it returns to the header sheet after it is opened, instead of the work sheet.

Any suggestions?

Regards,

Peter Moran

 

[rmikesmith]

Peter,

Odd behavior indeed. I have so far been unable to duplicate this. I will continue to investigate, but I see nothing in the code that would cause this, as there are only two application events captured, WindowActivate and WorkbookBeforeSave. Neither changes the active worksheet in a workbook.

Does the workbook in question use code to open to the correct worksheet or is the workbook simply saved that way?

Do you see anything odd if you open the workbook in question without the add-in loaded and then load the add-in?

Regards,
Mike

 

[jamierobinson]

Mike-

Great solution...

Please send me a copy of your add-in when you have time.

TIA

-jamie
jamierobinson(at)attbi.com

 

[PeterMoran]

Mike,

Thanks for your reply and subsequent email. Sorry re the delay.

I discussed this with one of my colleagues at work last week who agreed that there does not seem to be anything in your addin which would be responsible for the action of my spreadsheet.

My spreadsheet is a timesheet which contains an Auto_open module in the modules which checks date formats, that the Analysis Toolpack is loaded, and then if it is the start of a new month asks the user for appropriate static information for the timesheets for the month.

Secondly the "Daily" sheet, the one which is normally opened contains a Worksheet_Change module which enables times to be entered without the need for a colon.

I realise that you can't tell much, if anything, from what I have indicated above, so I would be happy to send you a copy if you wish.

Regards,

Peter Moran

 

[rmikesmith]

Peter,

Yes, please send me a copy of your workbook, as I am quite curious about this behavior. My guess is there is some odd interaction between the add-in and the Autpen sub. Thanks, and I'll let you know if I discover anything.

Regards,
Mike

rmikesmith@earthlink.net