Open the Earliest File in a Folder
The following procedure opens the earliest file in a folder...
'Force the explicit
delcaration of variables
'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
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
'Get the next Excel file from the folder
MyFile = Dir
'Open the latest file
Workbooks.Open MyPath & EarliestFile
Where to Put the Code
- Open the workbook in which to store the code.
- Open the Visual Basic Editor (Alt+F11).
- Insert a standard module (Insert > Module).
- Copy/paste the above code into the module.
- Return to Microsoft Excel (Alt+Q).
- Save the workbook.
How to Use the Macro
- Display the Macro dialog box (Alt+F8).
- Click/select the macro called "OpenEarliestFile".
- Click/select "Run".
Sample Workbook: Download