Can VB open the most recently created 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 that comes into my 'Refresh' Folder.

 

[Johnmcc]

Something along these lines should do it:

Sub GetMostRecentFile()
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
        
    'set path for files - change for your folder
    Const myDir As String = "c:\Refresh"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        If objFile.DateLastModified > dteFile Then
            dteFile = objFile.DateLastModified
            strFilename = objFile.Name
        End If
    Next objFile
    Workbooks.Open strFilename
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

Hi,

I used this section of code on Monday, with the file location changed to match my set up, and it worked perfectly. When I tried it this morning, I was met with the following error:

"Run time error 1004

'0.1 results.csv' could not be found. Check the spelling of the file name, and verify that the location is correct."

Neither the spelling of the file name, nor location have changed. I have Microsoft Runtime Scripting enabled.

Is there any reason why the macro would work on Monday but not today?

Thanks,

John