Open the Latest File in a Folder
The following macro opens the latest file in a folder...
'Force the explicit
delcaration of variables
Option Explicit
Sub
OpenLatestFile()
'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim Cnt As Long
'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)
'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 greater than the latest
'recorded
date, assign
its filename and date/time to variables
If LMD >
LatestDate Then
LatestFile
= MyFile
LatestDate
= LMD
Cnt
= Cnt + 1
End If
'Get the next Excel file from
the folder
MyFile
= Dir
Loop
'Open the latest file
If Cnt >
0 Then
Workbooks.Open
MyPath & LatestFile
Else
MsgBox
"No files were found in this folder...", vbExclamation
End If
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 "OpenLatestFile".
- Click/select "Run".
Sample Workbook: Download