File list details

[ChrisUK]

Hi,

Can someone help me fix this code please, I could sit here trying and maybe fix it by the time I go home .... or I can ask someone for help

I'm trying to list all the files under a single folder, for example all the files in MyDocuments and in each folder under MyDocuments

I want to return for each file name the date it was last modified as well as the name.

Sub getfiles()

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.Filename = "*.*"
If .Execute > 0 Then
Rows(x, 1) = fs.FoundFiles.Name
Rows(x, 2) = fs.FoundFiles.DateLastModified
End If
End With

End Sub

This is what I have so far which I'll admit I've taken from the help and tweaked it a little .... just not enough so it seems

Thanks in anticipation

Chris

 

[Andrew Poulsom]

See if you can adapt this:

Sub Test()
    Dim I As Long
    With Application.FileSearch
        .NewSearch
'       *** Change Folder name to suit ***
'        .LookIn = "C:\Testing"
        .LookIn = ThisWorkbook.Path
        .SearchSubFolders = False
        .Filename = "*.*"
        If .Execute > 0 Then
            For I = 1 To .FoundFiles.Count
                Cells(I, 1).Value = .FoundFiles(I)
            Next I
        End If
    End With
End Sub

 

[ChrisUK]

Thanks ... but (there is always a but)

I can see how you get the filename into the cell, but how can I get the saved date into the next cell?

So:

FileName|SavedDate

Thanks

Chris

 

[Andrew Poulsom]

Try (requires a reference to Microsoft Scripting Runtime):

Sub TestListFilesInFolder()
    Workbooks.Add ' create a new workbook for the file list
    Range("A1").Formula = "File Name:"
    Range("B1").Formula = "Date Last Modified:"
    ListFilesInFolder "C:\My Documents", True
End Sub
Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("A65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        Cells(r, 1).Formula = FileItem.Path
        Cells(r, 2).Formula = FileItem.DateLastModified
        r = r + 1
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Columns("A:H").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

 

[ChrisUK]

Thats perfect! Thanks