CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

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

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.

Upvotes: 0

Views: 986

Answers (1)

Marcucciboy2
Marcucciboy2

Reputation: 3261

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

Upvotes: 1

Related Questions