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

Topic

New Reply

When going to Page setup and for example the footer, I can insert the file name & 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

Reply | Quote

Replies

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:

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

Reply | Quote

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

Reply | Quote

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:

    Dim datSaved As Date
    datSaved = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time").Value
    ActiveSheet.PageSetup.LeftFooter = FormatDateTime(datSaved, vbShortDate)

Hope this helps! –Sam

Reply | Quote

Thanks a lot, I will try this as well.

Regards
Bj

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

another way…

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

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

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

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

Reply | Quote

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

Reply | Quote