Open newest downloaded file when multiple with same partial name

[cdalgorta]

Hi. What would be the macro to look in the downloads folder for all files that start with the partial name "Additional info looker Standard Unlimited*.csv" and then open the "most recent one"?

The code below always opens the oldest created(downloaded) file if there are 2 or more with the same partial name, instead of the newest one. Any way to make this reverse?
Thank you

Sub YLoadCSV_AdditionalInfo()
Dim CSV_FileToOpen      As String

CSV_FileToOpen = Dir(Environ("USERPROFILE") & "\Downloads\" & _
            "Additional info looker Standard Unlimited*.csv")                                   ' Save found CSV file to CSV_FileToOpen

  Workbooks.Open Filename:=Environ("USERPROFILE") & "\Downloads\" & CSV_FileToOpen            ' Open the CSV file

End Sub

 

[dave3009]

Give this a try

Option Explicit

'****************************************************************
'*  Purpose: Open the most recent download of file              *
'*  Filename: "Additional info looker Standard Unlimited*.csv"  *
'*  Author: dave3009                                            *
'*  Date: 2022-09-26                                            *
'****************************************************************

Sub LoopThroughFiles()

Dim strFileSpec As String
Dim strFileName As String
Dim cTime As Double
Dim tempFile As String
Dim strFolder As String

Const strSearch As String = "Additional info looker Standard Unlimited" 'change this to suit the filename

strFolder = Environ("USERPROFILE") & "\Downloads\"                      'Folder where you are searching
strFileSpec = strFolder & "*.*"                                         'set a generic specification for all files

strFileName = Dir(strFileSpec)                                          'get the directory

cTime = 0                                                               'set a start point for getting the time

Do While Len(strFileName) > 0                                           'D0 loop over the files
    If Left(strFileName, Len(strSearch)) = strSearch Then                                'test the filename
        If CDbl(FileDateTime(strFolder & strFileName)) > cTime Then     'test the datetime of the file
            cTime = CDbl(FileDateTime(strFolder & strFileName))         'store the details if this is most recent
            tempFile = strFileName
        End If
    End If
    strFileName = Dir
Loop

Workbooks.Open strFolder & tempFile                                     'open the most recent file
End Sub

 

[cdalgorta]

Give this a try

Option Explicit

'****************************************************************
'*  Purpose: Open the most recent download of file              *
'*  Filename: "Additional info looker Standard Unlimited*.csv"  *
'*  Author: dave3009                                            *
'*  Date: 2022-09-26                                            *
'****************************************************************

Sub LoopThroughFiles()

Dim strFileSpec As String
Dim strFileName As String
Dim cTime As Double
Dim tempFile As String
Dim strFolder As String

Const strSearch As String = "Additional info looker Standard Unlimited" 'change this to suit the filename

strFolder = Environ("USERPROFILE") & "\Downloads\"                      'Folder where you are searching
strFileSpec = strFolder & "*.*"                                         'set a generic specification for all files

strFileName = Dir(strFileSpec)                                          'get the directory

cTime = 0                                                               'set a start point for getting the time

Do While Len(strFileName) > 0                                           'D0 loop over the files
    If Left(strFileName, Len(strSearch)) = strSearch Then                                'test the filename
        If CDbl(FileDateTime(strFolder & strFileName)) > cTime Then     'test the datetime of the file
            cTime = CDbl(FileDateTime(strFolder & strFileName))         'store the details if this is most recent
            tempFile = strFileName
        End If
    End If
    strFileName = Dir
Loop

Workbooks.Open strFolder & tempFile                                     'open the most recent file
End Sub

Thank you so much Dave! It worked perfectly!