Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Full pathname in Footer (Excel 2000)

    Hi All,

    Is there any way to put the full path name in the footer of an excel spreadsheet. I can put filename.


    Thanks Kindly,

    Kerrie <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full pathname in Footer (Excel 2000)

    The following code, placed in the Workbook Before Print event routine will put the file path and file name into the center footer.

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.CenterFooter = ActiveWorkbook.FullNane
    End Sub
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full pathname in Footer (Excel 2000)

    Hi Legare,

    Where do I find the Workbork Before Print event routine - to place the code?
    Thank you for you help,

    Kerrie <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full pathname in Footer (Excel 2000)

    You can find it via Tools >> Macro >> Visual Basic Editor, double click the ThisWorkbook module in the VBAproject , then use the left drop down box to choose 'Workbook' . Now in the right drop down box, you will find all events for the workbook, including the BeforePrint event.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full pathname in Footer (Excel 2000)

    Hi Legare,

    Thanks for the code, it worked, Yippee <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    I was wondering, is there an easier way using Excel 2000? Just incase people are not familiar with VB.

    Kerrie
    <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Full pathname in Footer (Excel 2000)

    There's Rob Bruce's <A target="_blank" HREF=http://www.rb-ad.dircon.co.uk/rob/excelvba/utils/index.htm>FullPath</A> utility which installs as an add-in instead.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full pathname in Footer (Excel 2000)

    Not that I know of.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full pathname in Footer (Excel 2000)

    Hi Hans Pottel,

    Thank you for your assistance, it worked like a charm.
    Can I ask another question, is it possible to setup the macro so it is attached to a toolbar button so I can run it in any excel file?

    Thanks Kindly,

    Kerrie <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full pathname in Footer (Excel 2000)

    Kerrie,

    The best way to do this is making an Add-in. Add the following code to the ThisWorkbook module (here I add the menuitem to the 'View' menu (ID = 30004), with caption 'FullName in Header'; actually it is the LeftHeader, but change the code as you want):

    <pre>Private Sub Workbook_Open()
    DeleteMenuItem
    AddMenuItem
    End Sub
    </pre>


    <pre>Sub DeleteMenuItem()
    On Error Resume Next
    Application.CommandBars(1).FindControl(ID:=30004). Controls("FullName in Header").Delete
    End Sub
    </pre>


    <pre>Sub AddMenuItem()
    Dim ViewMenu As CommandBarPopup
    Dim NewMenuItem As CommandBarButton
    Call DeleteMenuItem
    Set ViewMenu = Application.CommandBars(1).FindControl(ID:=30004)
    If ViewMenu Is Nothing Then
    MsgBox "Can't find View on the Menubar"
    Exit Sub
    Else
    Set NewMenuItem = ViewMenu.Controls.Add(Type:=msoControlButton)
    NewMenuItem.Caption = "FullName in Header"
    NewMenuItem.OnAction = "change_header"
    NewMenuItem.FaceId = 136
    NewMenuItem.BeginGroup = True
    End If
    End Sub
    </pre>


    Then add a general module and add the following procedure to it:

    <pre>Sub change_header()
    On Error Resume Next
    With ActiveSheet.PageSetup
    .LeftHeader = ActiveWorkbook.FullName
    End With
    End Sub
    </pre>


    Now, follow the steps below to make an add-in of your workbook:
    1) in the VBE, select the VBAproject and click the right mouse button; select poperties
    2) select the protection tab and lock the project for viewing and add a password (and confirm it) for your add-in (you now protect your code from being viewed or edited by other users)
    3) click ok and choose file >> close and return to Excel.
    4) select File >> properties: fill in the title and the comments section. Both will appear in the dialog box of available add-ins.
    5) choose file >> save as and save your workbook as an xla file.

    Now go to Tools >> Add-ins and Browse to the new xla file. Excel will ask you to copy it to its default add-in directory. Accept and the file should become available. Check if you see the new menuitem under 'View'. (you may need to restart Excel)

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full pathname in Footer (Excel 2000)

    Hi Hans Pottel,

    I have added the code and its works perfectly, however if I want to change it - make the filename appear in the center footer where do I go to edit it. I have tried Tools, Macro and Tools, Macros, VB Editor and can't find it.

    Thanks Kindly,

    Kerrie

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full pathname in Footer (Excel 2000)

    of course, Kerrie, it is an add-in now. To change it, you should go to Tools >> Macros >> VB Editor and look in the poject window (if the project explorer is not visible, you should goto View and select Project Explorer). Now look for the VBAproject which contains the code of the add-in (it is the name of the xla file you gave it when you saved it). Double click it and you will be prompted with a message asking the password. When you enter the password, your code will become visible (the code you want to change is in the general module ).

Posting Permissions

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