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
- 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