Get the last modified file or folder in a directory with dynamic name

Question

I am trying to display the last person who accessed excel file and time in excel sheet but the file name changes every day (file name contains date).

My current codes gives me an error of file not found which I tried both ActiveWorkbook.path and Application.ActiveWorkbook.Path.

Sub Main()
'Setting Summary Report
Dim sAuthor As String
sAuthor = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
 'Last modified person & time
    Dim fileModDate As String
    Dim fs
    Dim f
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(Application.ActiveWorkbook.Path)
    fileModDate = f.DateLastModified
    Worksheets("Sheet1").Range("A2") = sAuthor & " " & fileModDate
End Sub

I could re-save the file as an uniform name like test or something, but I wasn't sure if the codes can be modified for this to work so I don't have to re-save the file.

You need to change the 'GetFile' parameter - you are missing the file name in it. Try this: 'Application.ActiveWorkbook.Path & "\" & ActiveWorkbook.Name' — Dawid SA Tokyo, Sep 26 '18 at 15:21
you'll have to do `Activeworkbook.FullName` instead. `.Path` does not include the name of the file — Marcucciboy2, Sep 26 '18 at 15:21
Thank you, the revised code will work with dynamic names. Thank you both. — sc1324, Sep 26 '18 at 15:38
you can use my answer [here](https://stackoverflow.com/a/52297421/6241235) — QHarr, Sep 27 '18 at 16:56

score 1 · Accepted Answer · answered Sep 27 '18 at 16:44

Guess I'll post an answer just to close this one out

Sub Main()
    'Setting Summary Report

    'Last author
    Dim sAuthor As String
    sAuthor = ActiveWorkbook.BuiltinDocumentProperties("Last Author")

    'Last modified time
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim file As Object
    Set file = fso.GetFile(ActiveWorkbook.FullName)

    Dim fileModDate As String
    fileModDate = file.DateLastModified

    'Updating range to last author and modified time
    Worksheets("Sheet1").Range("A2").Value2 = sAuthor & " " & fileModDate

End Sub

Get the last modified file or folder in a directory with dynamic name

1 Answers1