List of files in chronological order

Goodday

I have a list of CSV files -- I know how to get the list of the file names into an array and then start to read each file. However I would like the order of the file names in my list to be in the order Oldest to Newest. Below is the code I use for the list of files- any help is much appreciated

Code:

---

Function GetFileList(FileSpec As String, FileArray() As Variant) As Variant
    'Where    FileSpec = PathToFiles & "\*.csv"

    Dim FileCount As Integer
    Dim FileName As String
    On Error GoTo NoFilesFound
    FileCount = 0
    FileName = Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function
'  Error handler
NoFilesFound:
    GetFileList = False
End Function

---

Hi

Here is one way..

Code:

---

Function GetFileList(FileSpec As String) As Variant
    'Where    FileSpec = PathToFiles & "\*.csv"
   
    Dim i      As Long
    Dim Fldr    As String
    Dim Extn    As String
    Dim fl, f(), j As Long
    Dim wbk    As Workbook
   
    Fldr = Left$(FileSpec, InStrRev(FileSpec, "\"))
    Extn = Replace(FileSpec, Fldr, vbNullString)
    With CreateObject("scripting.filesystemobject").getfolder(Fldr)
        ReDim f(1 To .Files.Count, 1 To 2)
        For Each fl In .Files
            If fl.Name Like Extn Then
                i = i + 1
                f(i, 1) = fl.Name
                f(i, 2) = fl.DateLastModified
            End If
        Next
        Set wbk = Workbooks.Add
        With wbk.Worksheets(1)
            .[a1].Resize(UBound(f, 1), 2) = f
            .[a1].Resize(UBound(f, 1), 2).Sort .Cells(1, 2), 1
            GetFileList = .[a1].Resize(UBound(f, 1))
        End With
        wbk.Close 0: Set wbk = Nothing
    End With
End Function

---

use the oneliner:

Code:

---

sub M_snb()
  sn=split(createobject("wscript.shell").exec("cmd /c dir G:\OF\*.csv /b /o-d").readall,vbcrlf)
end sub

---

The resulting array sn has been sorted by filedatetime.