Open Latest File in a folder

[investinthebest]

This is what I'm trying to accomplish:
open latest file in a folder, copy the contents to a "master file" on a new tab, close the latest file, then find another latest file from a different folder, wash and repeat the above about 10 more times.

This will take a bunch of separate spreadsheets in different locations and put them all into one nice "master file" for me. I found some code that works great for one file, but I can't get the 2nd (and so on) files to work. The macro just pulls in the first file and stops, no errors, but doesn't give me additional tabs either. Below is the code I'm using - one can see I just copied and pasted the code a second time for "variable 2" but changed the variable names and location of the file. Can anyone please help me?

Sub OpenLatestFile()
'Variable 1
'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date

'Specify the path to the folder
MyPath = "C:\Users\abc\Desktop\Excel Test2\"

'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 & "*.xlsx", vbNormal)

'If no files were found, exit the sub
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If

'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
End If

'Get the next Excel file from the folder
MyFile = Dir

Loop

'Open the latest file
Workbooks.Open MyPath & LatestFile

'Move that tab to the Master File
ActiveSheet.Copy Before:=Workbooks( _
"latestFileMacroTest.xlsm").Sheets(1)

'Close the file
ActiveWorkbook.Close False


'Variable 2
'Declare the variables
Dim MyPath1 As String
Dim MyFile1 As String
Dim LatestFile1 As String
Dim LatestDate1 As Date
Dim LMD1 As Date

'Specify the path to the folder
MyPath1 = "C:\Users\abc\Desktop\Excel Test\"

'Make sure that the path ends in a backslash
If Right(MyPath1, 1) <> "\" Then MyPath1 = MyPath1 & "\"

'Get the first Excel file from the folder
MyFile1 = Dir(MyPath1 & "*.xlsx", vbNormal)

'If no files were found, exit the sub
If Len(MyFile1) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If

'Loop through each Excel file in the folder
Do While Len(MyFile1) > 0

'Assign the date/time of the current file to a variable
LMD1 = FileDateTime(MyPath1 & MyFile1)

'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD1 > LatestDate1 Then
LatestFile1 = MyFile1
LatestDate1 = LMD1
End If

'Get the next Excel file from the folder
MyFile1 = Dir

Loop

'Open the latest file
Workbooks.Open MyPath1 & LatestFile1

'Move that tab to the Master File
ActiveSheet.Copy Before:=Workbooks( _
"latestFileMacroTest.xlsm").Sheets(1)

'Close the file
ActiveWorkbook.Close False

End Sub

 

[investinthebest]

Great tips, thanks for all the help. My code is working like a charm now.

 

[Teeroy]

You're welcome.

 

[investinthebest]

You're welcome.

Teeroy, do you have any idea how to do the same thing except for open the 2nd most recent file (i.e. the one that is immediately older than the latest)? I would imagine there could be a subroutine that I could add folder names in B1, B2, etc to look for 2nd most recent file and add them to new tabs themselves.

Some of the folders I'm pulling lists from I only need the most recent file, but there are a couple that need the 2 most recent files. My workaround has been to place the 2nd most recent in it's own folder and use this code - but if there is an easy way to just pull both from the same folder, that would be much better for me.

 

[Teeroy]

Use new variables LMD2 and LatestFile2 to hold the second latest file details. You'll need to check each date against LMD and when a new date goes into LMD the previous data will be pushed down into LMD2. If the new date is not greater than LMD then you check against LMD2 and if it is greater then redefine this and LatestFile2 with the new information.

Try that and let me know how you go.

 

[investinthebest]

Use new variables LMD2 and LatestFile2 to hold the second latest file details. You'll need to check each date against LMD and when a new date goes into LMD the previous data will be pushed down into LMD2. If the new date is not greater than LMD then you check against LMD2 and if it is greater then redefine this and LatestFile2 with the new information.

Try that and let me know how you go.

Great tip, but I believe that will pull LMD and LMD2 for each folder. What I was thinking was:
Give me latest file for each folder in column A.
Give me second latest file for each folder in Column B (as not all folders in column A need both latest and '2,' but some do - so I thought I could duplicate folder path in column B and point there for '2'), but still put them on their own separate tabs.

 

[Teeroy]

Yes you will get LMD2 for all directories but the trick is to put a 2 next to the ones you want to get both files for, check for it, and if it is there run the copy for LMD2 as well. If you want to post your latest code I'll amend it for you.