Amended code to look at latest .xlsx or csv file

[howard]

I would like to amend code below to include .xlsx or .csv file, and import whichever is the latest file

Your assistance is most appreciated

 Sub OpenLatestFile()

   
   Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Dim lngLastRow As Long
    
    'Specify the path to the folder
  MyPath = "C:\Sales_extract"

    '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 & "*sales*BR*.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
    
    With Sheets(1)
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A3:O" & lngLastRow).Copy Destination:=ThisWorkbook.ActiveSheet.Range("A2")
        Application.CutCopyMode = False
    End With
    
    ActiveWorkbook.Close savechanges:=False 'Close the *.csv without saving
    
End Sub

 

[Domenic]

First, change the filter for your files as follows . . .

    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*sales*BR*.*", vbNormal)

Then, just in case you have other files with the same naming convention but a different extension, include an IF statement within your loop to filter for .xlsx and .csv files . . .

    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
   
        If LCase(Right(MyFile, 5)) = ".xlsx" Or LCase(Right(MyFile, 4)) = ".csv" Then
       
            '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
           
        End If
           
        'Get the next Excel file from the folder
        MyFile = Dir
       
    Loop

Hope this helps!

 

[howard]

Many thanks for the help. Code works perfectly

 

[Domenic]

That's great, I'm glad I could help.

Cheers!