Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show last modified in Footer (Excel 2K)

    We have a Excel form that we need to have the last modified date in the footer. It seems that this should be a simple and common setup, but haven't been able to figure out how to set it up. Any suggestions?

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

    Re: Show last modified in Footer (Excel 2K)

    Tom, you can show the last saved date in the footer using a macro, using the technique described <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=104341>here</A>. I don't know how to show last accessed date, but I think last saved is as close to last modified that you can get.

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.CenterFooter = "Last Saved: " & FileDateTime(ThisWorkbook.FullName)
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Show last modified in Footer (Excel 2K)

    You can use the Scripting FileSystemObject to obtain the active workbook file's Date Created, Date Last Accessed, and Date Last Modified properties. In the VB Editor, set a reference to Microsoft Scripting Runtime (SCRRUN.DLL) (Tools menu, References). The sample routine below prints these properties in the Debug window:
    <pre> Sub TestFileObjectProperties()

    Dim fso As Scripting.FileSystemObject
    Dim f As Scripting.File
    Dim x As Date, y As Date, z As Date

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile(ThisWorkbook.FullName)

    x = f.DateCreated
    y = f.DateLastAccessed
    z = f.DateLastModified

    Debug.Print "Date Created: " & x & vbCrLf & _
    "Date Last Accessed: " & y & vbCrLf & _
    "Date Last Modified: " & z

    Set fso = Nothing
    Set f = Nothing

    End Sub</pre>

    As recommended in previous reply from John, you can use the Workbook_BeforePrint event to display this information in the workbook's footer.
    HTH...

  4. #4
    New Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show last modified in Footer (Excel 2K (& 97))

    Can I just say this has helped me lots today
    As a complete novice to excel macros I was unsuccessfully ferreting about in ActiveWorkbook.BuiltinDocumentProperties to try & get a last saved date, but the FileDateTime(ThisWorkbook.FullName) works OK
    Thanks

Posting Permissions

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