Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Date in header indicating the last save.

    Do you know of a procedure that will place the date in a header to indicate the last date an Excel document is saved instead of the date it was printed. Any ideas would be appreciated.
    Last edited by workingonit; 2012-01-11 at 17:39.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    WOI,

    If you're using Excel 2010 Click on View Tab --> Page Layout
    Scroll to bottom of one page and click on "Click to add footer"
    Click on the Design Tab it it doesn't show automatically
    Click on Current Date add space and Click on Current Time if desired.

    Only problem w/this is it will update everytime the file is opened so you can't see when it was last saved!
    Most likely you'll need a macro that executes on the Before Save event and places the current date/time in the footer as text. If I get a chance I'll work one up.
    Last edited by RetiredGeek; 2012-01-11 at 18:09.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    WOI,

    Ok, here's a macro that will do what I think you want. This macro will change the header of the Excel file it resides in to the current date if the workbook is Saved. If you close the workbook w/o saving the date will remain unchanged. Make sure to put the following code in the This Workbook Module in the VBE.
    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
            Cancel As Boolean)
            
        Cancel = False
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = "Last Update " & Format(Now(), "mm/dd/yyyy")
        End With
        
    End Sub
    Note you can change it to add the time or move the code to be in the left of center section of the header, that's why I retained those elements in the code.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    workingonit (2012-01-27)

  5. #4
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Alternatively, use scripting.

    dim objFso as object, objFile as object

    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFso.GetFile("YourFile.xls")
    Debug.Print objfile.DateLastModified

  6. #5
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    It worked....Thank you!!

  7. #6
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    It worked....Thank you!!!

Posting Permissions

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