Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Modified XL 2000 (SP3)

    Date Modified in the properties of XL workbooks appears to be the same as Date Accessed even if the last access is only a look and no changes were made; and date last saved does not seem to be available in VBA. Is this correct? If so is it possible to write a macro that records the correct date last modified or the date last saved?

    TIA

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

    Re: Date Modified XL 2000 (SP3)

    Problem is that as soon as you open an Excel workbook, Excel changes the Last Modified date/time to the current date and time, so Last Modified and Last Accessed coincide.
    When you close the workbook, Last Modified is either restored to the original value if the workbook isn't being saved, or updated if the workbook is saved.
    In other words, the Last Modified date/time is only accurate if the workbook is closed!

    You can retrieve the Last Modified date of a closed workbook using Scripting.FileSystemObject or the function from <post:=682,264>post 682,264</post:>: GetProperty("...MyFile.xls", 3).

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Modified XL 2000 (SP3)

    Thank you Hans

    Only being able to access the info when the file is closed is a very useful feature.

    I am not familiar with user defined functions, am I doing this right, I copied the function into a module in my workbook then in a cell in my workbook I typed:

    =GetProperty(ActiveWorkbook, 4)

    and I get a type mismatch error (same when I put the full path instead of ActiveWorkbook)

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

    Re: Date Modified XL 2000 (SP3)

    ActiveWorkbook can only be used in the Visual Basic Editor, moreover you should use ActiveWorkbook.FullName there. In a formula, you should use

    =GetProperty("C:ExcelSomeWorkbook.xls", 3)

    with the appropriate path and filename substituted.

    Note that the path and filename are enclosed in double quotes ".

Posting Permissions

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