Open Last Modified File macro getting Error 1004

[curmudge0n]

*Sorry as I posted this already, marked as resolved after finding a small fix...which ultimately didn't work.

Macro purpose: Open the last modified file in a folder in the current file's path, do some modifications to the worksheet (which I removed from the code below), save the file in a different folder. It then selects the macro file and closes it.

Problem: After a single (sometimes several) successful runs of the macro, the macro is no longer able to "find" the last modified file, giving error 1004.

In order for me to have the macro work again, I have to open the last modified file and save via "SaveAs."

Error occurs on the following line after the successful run(s).
Workbooks.Open strFilename


Thank you and sorry for the re-post.

Sub Macro1()

    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
        
    Dim myDir As String
    myDir = ThisWorkbook.Path & "\Reports\"
    
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    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


    <random worksheet="" modifications="">


    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Reports_Name\" & "Name_" & ActiveWorkbook.Name & ".xlsx"
    Windows("Tester").Activate
    ActiveWorkbook.Close
    
End Sub

</random>

 

[gsbelbin]

This happens when there are no files in the folder which the code is looking at. I would suggest putting a break at the line

For Each objFile In myFolder.Files

and a watch on myFolder. This will let you see what folder it's looking at and all the other attributes including "Files".

Cheers

Gordon

 

[Norie]

Could it be that the code is trying to open a workbook that's already open?