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

    inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    Hi,

    I need code to insert the last saved date and time in a spread sheet.
    Can someone help me?

    Thanks!!!

    bjorn

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

    Re: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    Check out <!post=this post,273652>this post<!/post>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    You can create a function that will return the last saved date and time of the active workbook. If you put this function in a standard module in your personal macro workbook Personal.xls, it will always be available. Here is the code:

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

    You can use it in a cell in a formula:

    =PERSONAL.XLS!SaveDateTime()

    Don't forget to format the cell as a date, or time, or both. The formula will return #VALUE if the workbook has never been saved.

    Remark: if you use a non-english version of Excel, you may have to replace "Last Save Time" by the local equivalent (and Personal.xls may have another name too.)

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

    Re: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    <hr>Remark: if you use a non-english version of Excel, you may have to replace "Last Save Time" by the local equivalent <hr>
    Or use 12 as the argument:

    <pre>Function SaveDateTime() As Date
    SaveDateTime = ActiveWorkbook.BuiltinDocumentProperties(12)
    End Function</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    Thanks, I noted that in the post you referred to (after I had posted my own reply). Good idea.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    Hi Hans, I'm trying to use the function you set out for inserting the last saved date/time in a cell, which I have currently in a module in the spreadsheet as:

    Function SaveDateTime() As Date
    SaveDateTime = ActiveWorkbook.BuiltinDocumentProperties(12)
    End Function

    I don't want it in personal.xls because it's moved around between machines. Then, in a cell formatted as a date, I have =SaveDateTime().

    However, no matter what I do, it returns the date as 1 September 2002, 1:30 am!! I'm quite willing to believe I did edit it then (it's several years old), but it's an ongoing spreadsheet and was last saved today.

    What am I doing wrong?! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Beryl M


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

    Re: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    Hi Beryl,

    It might be that property #12 is the Last Save Time in Excel 2000/2002, but something else in Excel 97. You're probably getting the Creation Date instead of the Last Save Time. Try using the name of the property instead of its index number:

    ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")

  8. #8
    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: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    Hans' supposition is incorrect. In XL97 BuiltinDocumentProperties("Last Save Time") is #12.

    "Late save time" however, is one of the properties that XL does not keep current (at least in XL97). If you chose to use this property you must assign the value whenever you save the file (add this code to a beforesave event for any file you want to use the property with:

    ThisWorkbook.BuiltinDocumentProperties("Last Save Time").Value = Now

    Then when you call the property it will be current.
    If you never run a line like this, (in XL97, at least) you will get an error since the property is "empty"
    If you ran a line like this on "1 September 2002, 1:30 am" and never ran it again, it will continue to read this time.

    Perhaps some of the versions keep it "up-to-date", and others do not. If you use it with the earlier versions (which I know do not update it) the date will not be changed (automaticlly) when someone saves it. You will have to use code like above.

    An alternative is to use the filesystem object. This has a "problem" though that you can only determine the "modification data" when the file is closed. If the file is open it reads the current date/time (I think it is a Windows issue, since explorer does the same thing).

    Steve

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    Hi Hans, thanks for your reply, but I did use 'Last Save Time' originally, and only changed it to #12 when that didn't work, thinking the same thing you did, that it wasn't called that in XL97!

    I'll try Steve's suggestion and see if that can me the information I need, which is the date/time that the spreadsheet was *last* updated, when I open it *this* time!
    Beryl M


  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    Hi Hans/Steve, just to let you know I've found a way to do it - a combination of both of your suggestions, although neither worked on their own! Although the BeforeSave updated SaveDateTime, it refused to display in the cell (=SaveDateTime()), which continued to read 1 Sep 2002 no matter what, even after closing and reopening.

    I now have the SaveDateTime function Hans suggested in a module, and these two macros in ThisWorkbook - needless to say A2 is where I wanted to display the last saved date/time!

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.BuiltinDocumentProperties("Last Save Time").Value = Now
    End Sub

    Private Sub Workbook_Open()
    Range("a2").Value = SaveDateTime
    End Sub

    So, many thanks to both of you
    Beryl M


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

    Re: inserting LastSavedDate/Time (Win 2000, Excel 2002 SP-2)

    Hi Steve,

    Thanks for checking in Excel 97. In Excel 2002, Last Save Time is updated automatically; I don't think I ever used this property when I had Excel 97, so I didn't know that it was different there.

    Excel is a bit strange in that it sets the Last Modified date to Now when a workbook is opened, and restores the original date if the workbook is closed without saving it. Word and PowerPoint don't do this, they change the Last Modified date only when a document or presentation is saved.

Posting Permissions

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