Return Date Modified for File in Directory

[Mitch3]

How can I return the path, file name, and date modified if a file exist in a specific location? Is it possible with a macro?

 

[Andrew Poulsom]

You can use the FileDateTime function:

MS Excel: FILEDATETIME Function (VBA)

 

[patel45]

Sub Filedir()
        Range("A7:A100").ClearContents
        Cells(7, 1).Value = "File Name"
        Cells(7, 2).Value = "DateCreated"
        Cells(7, 3).Value = "DateLastModified"
        Cells(7, 4).Value = "DateLastAccessed"
        Cells(7, 5).Value = "File size"
        fpath = "E:\test\"   '<<<<<<<<<< to be changed
        Call ShowFolderList(fpath, 8, 1)
End Sub
Sub ShowFolderList(fpath, arow, col)

        Dim fs, f, f1, s, sf
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFolder(fpath)
        Set sf = f.SubFolders
        Set NFile = f.Files

        For Each pf1 In NFile
            If pf1.Name = "" Then Exit Sub
'            attr = pf1.Attributes
            Cells(arow, col) = pf1.Name
            Cells(arow, col + 1) = pf1.DateCreated
            Cells(arow, col + 2) = pf1.DateLastModified
            Cells(arow, col + 3) = pf1.DateLastAccessed
            Cells(arow, col + 4) = pf1.Size
            
            arow = arow + 1
        Next
    End Sub

 

[patel45]

with suggested function

Sub FiledirDate()
    Range("A2:A100").ClearContents
    Cells(2, 1).Value = "File Name"
    Cells(2, 2).Value = "Date"
    arow = 3
    fpath = "E:\prova\"   '<<<<<<<<<< to be changed
    fName = Dir(fpath & "*.xls*")
    Do While fName <> ""
      Cells(arow, 1) = fName
      Cells(arow, 2) = FileDateTime(fpath & fName)
      arow = arow + 1
      fName = Dir
    Loop
End Sub

 

[Mitch3]

Incredible! Thank you Both!

Patel45, that is exactly what I needed. Thank you / Grazie Mille!

 

[WMatkovich]

Does this work with files on a SharePoint?