Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Virginia, USA, Virginia, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display filename in Excel titlebar (Excel 2000)

    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" <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    Thanks in advance,
    gg

  2. #2
    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: Display filename in Excel titlebar (Excel 2000)

    Hi,
    Does <!post=this post,74959>this post<!/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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Virginia, USA, Virginia, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filename in Excel titlebar (Excel 2000)

    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... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks,
    gg

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

    Re: Display filename in Excel titlebar (Excel 2000)

    As an alternative, you can display the path+filename in a toolbar without using any code. See <!post=Phil Rabichow's tip, 107257>Phil Rabichow's tip<!/post> - it is in the Word forum, but works for Excel as well.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Virginia, USA, Virginia, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filename in Excel titlebar (Excel 2000)

    Phil's trick works... without complex coding! YEAH!!!

    Many thanks,
    gg

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filename in Excel titlebar (Excel 2000)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Virginia, USA, Virginia, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filename in Excel titlebar (Excel 2000)

    I will try this... <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    gg

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Virginia, USA, Virginia, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filename in Excel titlebar (Excel 2000)

    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

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filename in Excel titlebar (Excel 2000)

    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

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filename in Excel titlebar (Excel 2000)

    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?

  11. #11
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filename in Excel titlebar (Excel 2000)

    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

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

    Re: Display filename in Excel titlebar (Excel 2000)

    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

Posting Permissions

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