Excel VBA using FileSystemObject to list file last date modified

Question

this is my first time asking question so hopefully I'm following protocol. This is in reference to "get list of subdirs in vba" get list of subdirs in vba.

I found Brett's example #1 - Using FileScriptingObject most helpful. But there's one more data element (DateLastModified) I need in results. I tried to modify the code but keep getting invalid qualifier error. Here are code modifications I made:

1. Range("A1:C1") = Array("File Name", "Path", "Date Last Modified").
2. Do While loop added this => Cells(i, 3) = myFile.DateLastModified.

Will appreciate help to include the "Date Last Modified".

Santosh here is complete code with comments indicating modifications.

Public Arr() As String
Public Counter As Long

Sub LoopThroughFilePaths()
Dim myArr
Dim i As Long
Dim j As Long
Dim MyFile As String
Const strPath As String = "c:\temp\"
myArr = GetSubFolders(strPath)
Application.ScreenUpdating = False
'Range("A1:B1") = Array("text file", "path")' <= orig code
Range("A1:C1") = Array("text file", "path", "Date Last Modified") ' <= modified code
    For j = LBound(Arr) To UBound(Arr)
        MyFile = Dir(myArr(j) & "\*.txt")
        Do While Len(MyFile) <> 0
        i = i + 1
            Cells(i, 1) = MyFile
            Cells(i, 2) = myArr(j)
            Cells(i, 3) = MyFile.DateLastModified ' <= added to modify code
            MyFile = Dir
        Loop
    Next j
Application.ScreenUpdating = True
End Sub

Function GetSubFolders(RootPath As String)
Dim fso As Object
Dim fld As Object
Dim sf As Object
Dim myArr

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(RootPath)
For Each sf In fld.SubFolders
    Counter = Counter + 1
    ReDim Preserve Arr(Counter)
    Arr(Counter) = sf.Path
    myArr = GetSubFolders(sf.Path)
Next
GetSubFolders = Arr
Set sf = Nothing
Set fld = Nothing
Set fso = Nothing
End Function  

Answer 1

Try this code :

Sub ListFilesinFolder()

    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder
    Dim FileItem As Scripting.File

    SourceFolderName = "C:\Users\Santosh"

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    Range("A1:C1") = Array("text file", "path", "Date Last Modified")

    i = 2
    For Each FileItem In SourceFolder.Files
        Cells(i, 1) = FileItem.Name
        Cells(i, 2) = FileItem
        Cells(i, 3) = FileItem.DateLastModified
        i = i + 1
    Next FileItem

    Set FSO = Nothing

End Sub