Excel Macro: List Files From Directory Order By Date Modified

FileSystemObject does not order files, we can order files by Dir command. If you don't need to sort files, use FileSystemObject. First, we start with a simple macro and gradually improve the code.

List files from directory order by date modified

The following macro list files from a specific folder (D:\MyDocs) order by Date Modified:

Sub GetFile1()
    Dim WS As Object
    Set WS = CreateObject("wscript.shell")
    
    Dim MyFolderPath As String
    ' specific folder
    MyFolderPath = "D:\MyDocs"
    
    Dim arr
    ' list files from directory order by Date Modified
    arr = Split(WS.Exec("cmd /c Dir " & MyFolderPath & " /A:-D /B /O:-D /T:W").Stdout.ReadAll, vbCrLf)
    
    ' array to range
    ActiveCell.Resize(UBound(arr, 1)).Value = WorksheetFunction.Transpose(arr)
End Sub

dir /A:-D /B /O:-D /T:W means: no folders, bare format (no header/summary), order: date desc, timefield: Last written. For more information, read Dir command.

Order by date creation

If you want to order files by date creation, use /T:C:

arr = Split(WS.Exec("cmd /c Dir " & MyFolderPath & " /A:-D /B /O:-D /T:C").Stdout.ReadAll, vbCrLf)

Order by date last accessed

If you want to order files by date last accessed, use /T:A:

arr = Split(WS.Exec("cmd /c Dir " & MyFolderPath & " /A:-D /B /O:-D /T:A").Stdout.ReadAll, vbCrLf)

Sort by date and oldest first

If you want to sort by date and oldest first, use /O:D, no -:

arr = Split(WS.Exec("cmd /c Dir " & MyFolderPath & " /A:-D /B /O:D /T:A").Stdout.ReadAll, vbCrLf)

Including subolders

If you want all items, including subolders, add /S:

arr = Split(WS.Exec("cmd /c Dir " & MyFolderPath & " /A:-D /B /O:-D /T:W /S").Stdout.ReadAll, vbCrLf)

Filter files

If you want to list only xls files (xls, xlsx, xlsm... will be listed), add \*.xls:

arr = Split(WS.Exec("cmd /c Dir " & MyFolderPath & "\*.xls /A:-D /B /O:-D /T:W").Stdout.ReadAll, vbCrLf)

Select a folder instead of specifying a folder

If you don't want to specify the folder every time or avoid specifying the wrong folder, use the following code instead of MyFolderPath = "D:\MyDocs":

Sub SelectFolder()
    Dim InitialFoldr As String
    Dim MyFolderPath As String
    
    InitialFoldr = "D:\" '<<< Startup folder to begin searching from
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list files from"
        .InitialFileName = InitialFoldr
        .Show
        If .SelectedItems.Count <> 0 Then
            MyFolderPath = .SelectedItems(1) & "\"
            
            '...insert any processing code here...

        Else
            Exit Sub
        End If
    End With
End Sub

Full code:

Sub GetFile2()
    Dim WS As Object
    Set WS = CreateObject("wscript.shell")
    
    Dim InitialFoldr As String
    Dim MyFolderPath As String
    
    InitialFoldr = "D:\MyWorkBooks" '<<< Startup folder to begin searching from
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list files from"
        .InitialFileName = InitialFoldr
        .Show
        If .SelectedItems.Count <> 0 Then
            MyFolderPath = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
    
    Dim arr
    ' list files from directory order by Date Modified
    arr = Split(WS.Exec("cmd /c Dir " & MyFolderPath & " /A:-D /B /O:-D /T:W").Stdout.ReadAll, vbCrLf)
    
    ' array to range
    ActiveCell.Resize(UBound(arr, 1)).Value = WorksheetFunction.Transpose(arr)
End Sub

Add more file information

What if I want to add file information like file size, date last modified? The following macro do: List all files informations in the selected folder, including subfolders, sorted by last modified date.

Sub GetFile()
    ' List all files in the selected folder, including subfolders, sorted by last modified date

    Dim InitialFoldr As String
    Dim MyFolderPath As String
    
    InitialFoldr = "D:\" '<<< Startup folder to begin searching from
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list files from"
        .InitialFileName = InitialFoldr
        .Show
        If .SelectedItems.Count <> 0 Then
            MyFolderPath = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
    
    Application.ScreenUpdating = False 'Disable Screen Updating to speed up macro
    
    ' Table header
    Cells.Clear
    Range("A1:J1").Value = Array("#", "Name", "Attributes", "Path", "Size", _
        "Type", "Extension", "Date Created", "Date Last Accessed", "Date Last Modified")
    
    Dim LastBlankCell As Long
    LastBlankCell = Cells(Rows.Count, 1).End(xlUp).Row + 1 'Get the last blank cell of column A   
    
    Dim WS As Object
    Set WS = CreateObject("wscript.shell")
    Dim arr
     ' sort by Date Last Modified
    arr = Split(WS.Exec("cmd /c Dir " & MyFolderPath & " /A:-D /B /O:-D /T:W /S").Stdout.ReadAll, vbCrLf)
'    ' do not include subfolders
'    arr = Split(WS.Exec("cmd /c Dir " & MyFolderPath & " /A:-D /B /O:-D /T:W").Stdout.ReadAll, vbCrLf)

    Dim i As Integer
    Dim FileExtension As String
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    For i = 1 To UBound(arr) 'loop all files
        ' get file extension
        FileExtension = UCase(Split(arr(i - 1), ".")(UBound(Split(arr(i - 1), "."))))
                        
        Select Case FileExtension
            Case "XLS", "XLSX", "XLSM"    'Get "XLS", "XLSX", "XLSM" files
'                With FSO.GetFile(MyFolderPath & arr(i - 1))    'dir command without /S
                With FSO.GetFile(arr(i - 1))    'The dir command has /S
                    Cells(LastBlankCell, 1) = LastBlankCell - 1         '#
                    Cells(LastBlankCell, 2) = .Name                     'Name
                    Cells(LastBlankCell, 3) = .Attributes               'Attributes
                    Cells(LastBlankCell, 4) = .Path                     'Path
                    Cells(LastBlankCell, 5) = .Size                     'Size
                    Cells(LastBlankCell, 6) = .Type                     'Type
                    Cells(LastBlankCell, 7) = FileExtension             'Extension
                    Cells(LastBlankCell, 8) = .DateCreated              'Date Created
                    Cells(LastBlankCell, 9) = .DateLastAccessed         'Date Last Accessed
                    Cells(LastBlankCell, 10) = .DateLastModified        'Date Last Modified
                End With

                LastBlankCell = LastBlankCell + 1   'next row number

            Case Else
                                    
        End Select
        
    Next
   
    Cells.EntireColumn.AutoFit
    Application.ScreenUpdating = True
    Set WS = Nothing
    Set FSO = Nothing
End Sub

List first 20 files

List the 20 most recently modified files per folder.

How to Use This Macro

Most VBA code should be placed in Standard Modules unless specified.

If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?

The following steps teach you how to put VBA code into a Standard Module:

Activate the Visual Basic Editor by pressing ALT + F11.
Right-click the project/workbook name in the Project Window.
Choose Insert -> Module.
Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
Click Run button on the Visual Basic Editor toolbar.
For more information, learn this course: Programming with Excel VBA