Can I write a macro to copy and paste data from the most recent file in a folder into my open workbo-CodePudding

I run a large spreadsheet 3 times a week that essentially summarises business deliveries and other information. The sheet requires me to dump in 3 or 4 intake reports each time it's run to lookup the relevant data. I'm essentially trying to see I can create a macro that will open the most recent file in a folder and copy and paste the data into my active workbook. I can't get the code to open the file - I'm getting a run time error saying the file/path can't be found even though it is definitely correct

The code I'm using is below

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

'specify the path to the folder
Mypath = "C:\Users\Documents"

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

'Get the lfirst 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 folder
Do While Len(Myfile) > 0

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

End Sub

CodePudding user response：

The reason is simple: You never assign anything to LMD, so LMD is always 0 (that is the initial value for a date). As a consequence, LMD is never > LatestDate (which is also 0), you never assign any value to Myfile. At the end, you try to open a file with the name of your folder and that of course fails.

Simply add the FileDateTime command to fetch the file date:

    LMD = FileDateTime(Mypath & Myfile)
    If LMD > LatestDate Then
        LatestFile = Myfile
        LatestDate = LMD
    End If

Hint: Learn to use the VBA debugger to check for such problems. I recommend to watch https://www.youtube.com/watch?v=Um2JwZfwoFI, but you can find a lot of other resources.