Check Filename Before Opening

[JLouis]

Hello. I have the code shown below (ripped no doubt from someone on this board) to open the newest file inside a directory. I need to be able to check the partial filename to make sure the correct file was put in the correct folder before opening. If the file is the wrong one, I'd like a msgbox to inform me of this.

In this case, a downloaded file always has this begining "starburst-"...

Sub StarburstNewestFile()

    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Application.DisplayAlerts = False
Application.ScreenUpdating = False
        ' Dim path As String
    MyPath = ThisWorkbook.path & "\Exports\StarburstExport\"
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    MyFile = Dir(MyPath & "*.csv", vbNormal)
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop
    Workbooks.Open MyPath & LatestFile
End Sub

In short, throw a msgbox if the newest file in the folder doesn't start with "starburst-"

Thanks for looking/advising. I'd be lost without this resource.

 

[Micron]

perhaps
If LMD > LatestDate And Instr(MyFile,"starburst-")>0 Then

In Access you don't have to include start position; can't recall if you have to in Excel. Either way, you can inject the parameter:

If LMD > LatestDate And Instr(1,MyFile,"starburst-")>0 Then

Not sure I see the point of If Right(MyPath, 1) <> "\"
when your concatenated string ends with \
MyPath = ThisWorkbook.path & "\Exports\StarburstExport\"

 

[JLouis]

I wondered the same thing about the "/" but left it in as part of the snippet. I'll try the mod you suggest and report back. Thanks for the response.

 

[JLouis]

I tried it but it errored out. Can't find the file. Tried adding a wild card but knew that wouldn't work. I think this has something to do with using a wildcard for opening partial filenames:

Dir(ActiveWorkbook.Path & "\starburst-*.csv")

 

[JLouis]

I reserarched the instr function and it is quite powerful. Used your code on a different setup and it worked fine. Not sure why the first one didn't work. I'll test harder.

Anyway, I learned soemthing and found a solution. Win-WIn. Thank you Micron for youre help. Marked as solved.

 

[JLouis]

BTW, I found out why the first attempt failed to find the correct file. The code is case sensitive and needs to be exactly as the file is named. Good stuff.