Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    'SaveDate' function? (all)

    Is there in Excel any equivalent to Word's SaveDate field? =NOW() doesn't quite do what I want; I would like to specify "dd MMMM yyyy HH:mm", being the date and time that the workbook was last saved.

    Thanks!

    John
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'SaveDate' function? (all)

    Add this to the this workbook module. Format the cell as desired.

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheets("sheet1").Range("A1").Value = now
    End Sub</pre>


    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'SaveDate' function? (all)

    You can create a custom function

    Function LastModified() As Date
    LastModified = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
    End Function

    and use it in a cell like this:

    =LastModified()

    or if you put the function in your Personal.xls:

    =Personal.xls!LastModified()

    Format the cell with the formula the way you like (Format | Cells..., Number tab)

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'SaveDate' function? (all)

    Thanks very much to both of you!

    John
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'SaveDate' function? (all)

    I thought the Last save time property was not maintained by Excel (I remember it was problematic in XL97). Is it maintained and used in the later versions?

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'SaveDate' function? (all)

    It works OK in my Excel 2002 SP-3.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'SaveDate' function? (all)

    Yes it seems to work in my XL2002 as far as I have tested it.

    It fails (in XL2002) with older files that were saved in XL97 (it causes an automation error) since XL97 did not use the property. I don't know what version of XL started using the property.

    I know it does not work with XL97 and since John asked about All versions, this may be a concern to him.

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'SaveDate' function? (all)

    Good point!

Posting Permissions

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