Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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 :<pre> Application.Caption = ActiveWorkbook.FullName</pre>



    Andrew C

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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. <pre>Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
    Application.Caption = ActiveWorkbook.FullName
    End Sub</pre>

    Or you could use <pre>Sub Auto_Open
    Application.Caption = ActiveWorkbook.FullName
    End Sub</pre>

    in a general module.

    Andrew C

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    or create an application-level event handler. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

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

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    To trap Application Workbook Activate/Deactivate events in Excel, and to do what you want just requires a class module with the following code : <pre>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</pre>

    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 <pre>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</pre>

    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
    Attached Files Attached Files

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •