CODEWITHSUNDEEP
excelvba
con3lla
con3lla

Reputation: 3

How to open most recent file in a folder, to copy data from the most recent file in a folder into my open workbook?

I update a spreadsheet three times a week that summarises business deliveries and other information.

The sheet requires me to dump in three or four intake reports each time to lookup the relevant data. I want to open the most recent file in a folder and copy the data into my active workbook.

I can't open the file. I get a run time error saying the file/path can't be found.

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

Upvotes: 0

Views: 344

Answers (1)

FunThomas
FunThomas

Reputation: 29286

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.

Upvotes: 2

Related Questions