Find last save time of a closed workbook

Question

I am trying to make a macro that can reference when another workbook was last updated without opening it.

I found this one but it only works on the active workbook:

Function LastModified() as Date

  LastModified = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")

End Function

What I want is something like this:

Function LastModified() as Date

  LastModified = path."c:/test.xlsx".BuiltinDocumentProperties("Last Save Time")

End Function

But that didn't work of course. How do I fix this?

https://stackoverflow.com/questions/16627441/excel-vba-using-filesystemobject-to-list-file-last-date-modified — braX, Sep 27 '17 at 12:57
VBA does not support reading from closed documents. If you want to access built-in properties from a closed document, you will need to install the MS supplied DLL file called DSO OLE Document Properties Reader 2.1. See Chip Pearsons page on [Document Properties](http://www.cpearson.com/excel/docprop.aspx). Or you can use the `FileSystemObject` to access the `LastModified` time. Whether that will provide the same information as the built-in property is not known to me. But perhaps the result will be sufficient (and it is simpler to implement). — Ron Rosenfeld, Sep 27 '17 at 13:36

score 1 · Answer 1 · answered Sep 29 '17 at 18:04

1

The Filesystemobject worked but I needed then to print it to cells, as it would'nt call the function.

The FileDateTime worked for me though :)

Function LastModified() as Date

  LastModified = FileDateTime("filepath")

End Function

answered Sep 29 '17 at 18:04

Lars Høisveen

23
1
4

score 0 · Answer 2 · answered Sep 27 '17 at 13:53

Something like this should do the trick. You'll need to use the FileSystemObject in this example. I'm using late binding in the example, but you may get some performance improvements if you switch to early binding.

Option Explicit

Private Function LastModified(ByRef filePath As String) As Date
    'Set a default value
    LastModified = vbNull

    If Len(Trim$(filePath)) = 0 Then Exit Function

    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(filePath) Then LastModified = .GetFile(filePath).DateLastModified
    End With

End Function

Sub SOExample()
    'Specify a path to a file you want to get LastModified Date
    Dim filePath As String: filePath = "MyPathToAFile"

    'Return a date or vbNull
    Dim myDate As Date: myDate = LastModified(filePath)

    'Output the Date if it isn't vbNull
    If Not myDate = vbNull Then Debug.Print myDate
End Sub

Find last save time of a closed workbook

2 Answers2