Pulling the most recent file in a folder

[davidhall80]

I have a folder called 'Refresh'. Every week a new file is dropped into that folder. Instead of me placing that new file into my workbook, i would like my macro to just grab it. Is there a way for VB to grab the most recently created file in my 'Refresh' Folder.

 

[Harvey]

I'm not sure but I think there is no "normal" VBA function that covers file size. If you can change the filename to a date it would be really easy to fix it however.

 

[erik.van.geit]

Hi,

try this
This will open the most recent file with filename containing "John" in the folder "invoices".

Sub Test()
    With Application.FileSearch
        .NewSearch
        .LookIn = "D:\bus\acc\invoices" 'no backslash
        .Filename = "*John*.xls"
        .SearchSubFolders = True
        If .Execute(msoSortByLastModified, msoSortOrderDescending, True) > 0 Then
            Workbooks.Open .FoundFiles(1), xlWindows
        End If
    End With
End Sub

kind regards,
Erik