Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    ASKER, Norway
    Posts
    277
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Function of &[File] (Office 2000, W SR-1)

    When going to Page setup and for example the footer, I can insert the file name & <img src=/S/disk.gif border=0 alt=disk width=15 height=15> into one of the text boxes, but ...

    How do I control whether the file name should be shown with the whole path or just show only the file name?

    and

    how can I insert a field code giving the last saved date instead of the current date as the &[Date] gives???

    Thanks in advance!!

    Best regards
    Bj

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

    Re: Function of &[File] (Office 2000, W SR-1)

    I think the only way to control that is to create the footer using VBA code. The best way to do that is to put the code in the Workbook_BeforePrint event routine. The following code will put the full path and filename into the center footer of all sheets in the workbook:

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim oWB As Workbook
    For Each oWB In Workbooks
    oWB.Worksheets("Sheet1").PageSetup.CenterFooter = oWB.FullName
    Next oWB
    End Sub
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    ASKER, Norway
    Posts
    277
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Function of &[File] (Office 2000, W SR-1)

    Thanks a lot,

    I will try it.

    Strange that excel is so "bad" on this compared to Word.

    Have nice Week End

    Best regards
    Bj

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function of &[File] (Office 2000, W SR-1)

    As Legare states, you must use VBA. The only codes the Microsoft provides are Date, Page, Pages, File, and Tab. In addition to Legare's solution, you can insert this information just befor printing with the BeforePrint event. See Chip Pearson's site.

    To get the last saved date, use:
    <pre> Dim datSaved As Date
    datSaved = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time").Value
    ActiveSheet.PageSetup.LeftFooter = FormatDateTime(datSaved, vbShortDate)</pre>


    Hope this helps! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function of &[File] (Office 2000, W SR-1)

    another way...

    The following code allows you to control the whole path or just the file name:

    <font color=blue>
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    myPath = ActiveWorkbook.Path
    myName = ActiveWorkbook.Name
    myFullName = myPath & "" & myName
    For Each Sheet In Sheets
    Sheet.PageSetup.CenterFooter = myFullName
    Next Sheet
    End Sub
    </font color=blue>

    The following routine insert the date in the left foot at moment of save file

    <font color=blue>
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    myDate = Date
    For Each Sheet In Sheets
    Sheet.PageSetup.RightFooter = myDate
    Next Sheet
    End Sub
    </font color=blue>

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    ASKER, Norway
    Posts
    277
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Function of &[File] (Office 2000, W SR-1)

    Thanks a lot, I will try this as well.

    Regards
    Bj

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    ASKER, Norway
    Posts
    277
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Function of &[File] (Office 2000, W SR-1)

    Thanks!!

    Will try it and see what my "customers" say when the file contains a macro and they will have to say yes to open it.

    Our security setting is Medium.

    Regards
    Bj

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    ASKER, Norway
    Posts
    277
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Function of &[File] (Office 2000, W SR-1)

    Thanks a lot!

    I will try this as well as the other proposals. It will take some days to carry it out, have a lot of Quality Assurance work to do this week.

    Best regards
    Bj

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function of &[File] (Office 2000, W SR-1)

    If all users have XL2000 or up, you can sign your project. That way the users only have to say "yes" once, provided they check the box to trust everything from you.
    You must first create a personal signature using the file:
    Makecert.exe
    located in your office directory.
    Then in your workbook, in the VBE (with your project as the active project) choose Tools, Signature.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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