CODEWITHSUNDEEP
vbaexcel
Dr.YSG
Dr.YSG

Reputation: 7591

Excel VBA workbook property for last modified vs SaveDate

I am noticing users modifying excel workbooks but the date for the SaveDate is not being updated. We would like to know when the last updated their workbooks.

As far as I can tell the Workbook.BuiltinDocumentProperties collection does not include a property for ModifiedDate. Is there some way we can tell our users to save so that the SaveDate is updated?

Or can we actually get the Modified date from VBA?

Upvotes: 1

Views: 984

Answers (1)

Marcucciboy2
Marcucciboy2

Reputation: 3261

I don't believe that there is a native function to get a file's Date Modified date, but you can pretty easily use the windows scripting reference to FileSystemObject to get the File.DateLastModified property.

I'm not completely sure how you'd like to implement this, but here are two examples you could consider:

When the following example is triggered, it reaches the code to prompt the user if the ActiveWorkbook has unsaved changes and if the last save was longer than 15 minutes ago.

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim wb As Workbook
Set wb = ActiveWorkbook

If DateDiff("n", fso.getfile(wb.FullName).DateLastModified, Now) > 15 And wb.Saved = False Then
    'prompt user to save or save for them
End If

If there are potentially multiple workbooks open then you might want to use the next example which loops through all open workbooks before completing the same operation as above.

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim wb As Workbook
For Each wb In Application.Workbooks

    Dim wbFile As Object
    Set wbFile = fso.getfile(wb.FullName)

    Debug.Print "filename:  " & wbFile.name & vbCrLf & _
                "modified:  " & wbFile.DateLastModified & vbCrLf

    If DateDiff("n", wbFile.DateLastModified, Now) > 15 And wb.Saved = False Then
        'prompt user to save or save for them
    End If

Next wb

If for some reason you instead have to loop through a directory to look for files, this site gives a nice example on how to do so.

Upvotes: 1

Related Questions