Open the Earliest File in a Folder

The following procedure opens the earliest file in a folder...

'Force the explicit delcaration of variables
Option Explicit

Sub OpenEarliestFile()

    'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim EarliestFile As String
    Dim EarliestDate As Date
    Dim LMD As Date
    
    'Specify the path to the folder
    MyPath = "C:\Users\Domenic\Documents\"
    
    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*.xls", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    EarliestDate = Date + 1
    
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
        
        'If the date/time of the current file is less than the earliest
        'recorded date, assign its filename and date/time to variables
        If LMD < EarliestDate Then
            EarliestFile = MyFile
            EarliestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
        
    Loop
    
    'Open the latest file
    Workbooks.Open MyPath & EarliestFile
        
End Sub

Where to Put the Code

  1. Open the workbook in which to store the code.
  2. Open the Visual Basic Editor (Alt+F11).
  3. Insert a standard module (Insert > Module).
  4. Copy/paste the above code into the module.
  5. Return to Microsoft Excel (Alt+Q).
  6. Save the workbook.

How to Use the Macro

  1. Display the Macro dialog box (Alt+F8).
  2. Click/select the macro called "OpenEarliestFile".
  3. Click/select "Run".

Sample Workbook: Download

Home | Excel | VBA | Blog | About | Contact