VBA that references the most recent file in a folder

[Captain_Conman]

I am fairly new at VBA and am attempting to write a macro that references the most recent weekly report in a folder. My current code looks like this...

Workbooks.Open Filename:="S:\SherwinWilliams\Oracle Reports\6-13-2018.xlsx"
Windows("6-13-2018.xlsx").Activate

It works fine, however, later next week I will save a new report in the folder and want the macro to reference that report, and so on for weeks to come.

How can I tell my macro to reference the most recent report? Also, will I have to change my reference in the rest of my code? For example, will Windows("6-13-2018.xlsx").Activate have to change?

Thank you in advance for any help.

 

[tonyyy]

Captain Conman,

You might consider the following...

Sub MostRecentFyle_1059443()

Dim oFSO As Object, oFSOFolder As Object, oFSOFile As Object
Dim sFilePath As String
Dim ar() As Variant
Dim i As Integer, kounter As Integer
Dim MostRecent As Date
Dim MostRecentFyle As String

i = 1
kounter = 0
sFilePath = "S:\SherwinWilliams\Oracle Reports\"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFSOFolder = oFSO.GetFolder(sFilePath)
kounter = oFSOFolder.Files.Count
ReDim ar(1 To kounter, 1 To 2)

For Each oFSOFile In oFSOFolder.Files
    If InStr(oFSOFile.Name, ".xlsx") > 0 Then
        ar(i, 1) = oFSOFile.Name
        ar(i, 2) = FileDateTime(oFSOFile)
        If MostRecent <> 0 Then
            If ar(i, 2) > MostRecent Then
                MostRecent = ar(i, 2)
                MostRecentFyle = ar(i, 1)
            End If
        Else
            MostRecent = ar(i, 2)
            MostRecentFyle = ar(i, 1)
        End If
        i = i + 1
    End If
Next oFSOFile
    
Set oFSOFile = Nothing
Set oFSOFolder = Nothing
Set oFSO = Nothing
Erase ar

Workbooks.Open sFilePath & MostRecentFyle
End Sub

The code uses the FileDateTime function to return the Created or Last Date Modified field of each of the files in the specified folder, then creates a string variable - MostRecentFyle - that is the most recent file in the folder.

Cheers,

tonyyy