Retrieve latest template from folder

[eager_vba_novice]

Hello all

My boss stores the quote template in a folder. Each file in the folder is named the same except for a R and revision number at the end of the filename, ie...R1, R2...etc. The current revision if R19.

I have a macro button that opens the latest template but I have to manually change the code to match the rev.

Is there a way to retrieve the latest template revision without knowing the revision number?

Thank you and hope you're having a great day!

 

[John_w]

Try this, with the folder path and file name changed to suit.

    Dim n As Integer
    Dim latestFile As String
    
    n = 0
    Do
        n = n + 1
    Loop Until Dir("C:\path\to\folder\Template R" & n & ".xlsx") = vbNullString
    latestFile = "C:\path\to\folder\Template R" & n - 1 & ".xlsx"
    Debug.Print latestFile