Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding a pathname to a footer

    Excel: Once upon a time there was a add-in listed in the archives that automatically added the path/filename to the footer in Excel Worksheets. Does anyone remember where this came from? I can't seem to find it in the Search.

    Thanks

  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: Adding a pathname to a footer

    Hi Mary,
    There's one <A target="_blank" HREF=http://www.j-walk.com/ss/excel/files/general.htm>here</A> amongst other things.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a pathname to a footer

    For those of us that like to write our own code, here's a sample from Microsoft:
    <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q142/1/40.ASP>Microsoft KB Article Q142140</A>

    FWIW

    (http://support.microsoft.com/support.../Q142/1/40.ASP)

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a pathname to a footer

    I changed the microsoft solution to the following (extracted)

    font_set = "&""ATRotisSemiSans,Regular""&8" 'sets font size &8 = the font size
    activesheet.pagesetup.LeftFooter = font_set & ActiveWorkbook.Path & " " & "&F" & Chr(10) & "Sheet: &A"

    the &F = the filename, &A = sheetname
    This means that if the file is copied or renamed the footer stays upto date as long as it remains in the same directory.

    Anyone know why it takes so long for the code to update a header or footer?

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

    Re: Adding a pathname to a footer

    Just in case anybody prefers a Macro to an AddIn, I present the following which I have being using.

    Dim DateStamp As Date
    Dim CSheet As Object
    Dim WS As Worksheet


    Sub Footer()
    '
    ' Macro by Andrew Cronnolly 14/10/97
    '
    On Error GoTo ErrHandler

    If ActiveWorkbook.Path = "" Then
    GoSub GetFileName:
    End If
    DateStamp = FileDateTime(ActiveWorkbook.FullName) 'Get Time& date of last Save
    Application.ScreenUpdating = False 'Switch Off Screen Updating
    Set CSheet = ActiveSheet 'Bookmark current Active Sheet
    For Each WS In ActiveWorkbook.Worksheets() 'Cycle through all Work Sheets in Active Work Book
    WS.Activate
    With ActiveSheet.PageSetup
    .FooterMargin = Application.InchesToPoints(0.28)
    .LeftFooter = "&7" + ActiveWorkbook.FullName + " " + Chr(10) + ActiveSheet.Name 'Set Left Footer
    .CenterFooter = "&B&8Page &P of &N&B" ' Set Center Footer
    .RightFooter = "&7" + ActiveWorkbook.Author + Chr(10) + " " + Str(DateStamp) 'Set Right Footer
    End With
    Next
    CSheet.Activate 'Restore the Active Sheet
    Application.ScreenUpdating = True 'Reactivate Screen Updating

    Exit Sub

    GetFileName:
    Application.Dialogs(xlDialogSaveAs).Show
    Return

    ErrHandler:

    msgButtons = vbRetryCancel
    msgResult = MsgBox("Work Book must be saved" + Chr(10) + "in order to proceed", 5, "Warning")

    Select Case msgResult
    Case 4
    Resume GetFileName:
    Case 2
    Exit Sub
    End Select
    End Sub

    A couple of points to note :

    1 It adds the footer to every worksheet in the workbook (Can be altered just to cover the active sheet)

    2 It requests a Filename for an unsaved workbook.

    3 It also shows the last save date and time together with the author's name.

    All or any of these can be changed

    Andrew

  6. #6
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a pathname to a footer

    Thanks, I eventually found this site. However, when I download it, the anti-virus software takes exception to it and I can't run it.

  7. #7
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a pathname to a footer

    Thanks, I'll try this and check out Microsoft's solution as well.

Posting Permissions

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