Name WB Via Newest File

[JLouis]

So I do a seach in a directory to find the newest file that has been downloaded into the directory. Once that file has been identified, it is opened and processed into my workbook. If for some reason I download an incorrect file into the directory, it obviously screws with my process workbook. (Just happened) If I can identify an incorrect file before processing I can stop the code from finishing.

I'd like to be able to name this newest file before processing so I can run a test to see if it's the correct file file. I have another macro that processes the opened WB. If I have a name for this opened file, I can reference it in my "TestMe" macro. I may need to name it before opening, but after it has been identified as the newewst file.

Finally, maybe I have the wrong idea completely and would welcome a different approach. Thanks so much for all the help you folks provide.

    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
       If LMD > LatestDate = 0 Then GoTo finish
        If LMD > LatestDate  > 0 Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop
    Workbooks.Open MyPath & LatestFile
   ' TestMe Newest file is opened waiting to be used. I'd like to test it before processing.
    ActiveWorkbook.Activate
    Exit Sub

 

[Herakles]

I would use the file name as a way of identifying what is in the file.

The file name can have the date and time embedded within it but having an index number within the filename
would enable you to clearly identify which one is the latest.

 

[Dan_W]

I would use the file name as a way of identifying what is in the file.

The file name can have the date and time embedded within it but having an index number within the filename
would enable you to clearly identify which one is the latest.

I agree - seems like a good idea.

 

[JLouis]

Hello and thank you for the responses. I was afraid I didn't explain it very well.

When the file is found, it has a naming convention that changes each time a new file is downloaded. It has a different name each time. Once identified by date, it opens for processing. What I need is a method to name this file "New File.csv" as an example. Then I can refer to that file in another file for testing it's the right kind of file. Does that help?

 

[JLouis]

I fixed the problem. No harder than adding:

   Loop
    Workbooks.Open MyPath & LatestFile
   ' TestMe
   
   ActiveWorkbook.SaveAs MyPath & "NewFile.csv"
    ActiveWorkbook.Close
    Exit Sub

I can now reference the new file in my test macro. Much ado about nothing.

Thanks again.

 

[Dan_W]

Looks potentially problematic, but ok.