How to Open Newest File in a Folder in Microsoft Excel
In case you want a VBA code which will allow you to open the recently saved file in the folder with just a click on macro button.
Question): I have a team of 10 people & I am urgently looking for a macro that will help me in opening the latest file saved by team member so that I do not have to go the path wherein the file is saved.
We need to follow the below steps:
Option Explicit
Sub NewestFile()
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
MyPath = "C:\Users\Documents\"
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
MyFile = Dir(MyPath & "*.xls", vbNormal)
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If
Do While Len(MyFile) > 0
LMD = FileDateTime(MyPath & MyFile)
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If
MyFile = Dir
Loop
Workbooks.Open MyPath & LatestFile
End Sub
In this way, using macro code, we will be able to open the latest file saved by the team member.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Hi, thank you for your post!
I'm wondering if there is any way to find and attach to an email (or just open, as in this example), say 5, most recently created/saved files? What condition (and where) to put in order to reach my goal?
I would like to make it with "DIR Statement" and loop - this is crucial: DIR and Loop(s).
with very warm regards
Wojciech Szajnar
Hello,
Can you please advise if you have any code which allow the user to manually key in the file name on the msg box and import the data directly to another excel sheet?
Thanks,
Lawrence
The vba code is very useful.but can i know how to close the file after open?
Can we open 2nd latest file??