Selected Answer
This is not the simplest thing to do and it requires a couple macros. The second one sorts the dates and I just copy/pasted it in there.
Sub GetFileName()
Dim file, dateStart, myDate As Variant
Dim dateArray() As Date
file = Dir("D:\test\")
i = 1
While (file <> "")
dateStart = InStrRev(file, " ") + 1
myDate = Replace(Mid(file, dateStart, 10), ".", "/")
ReDim Preserve dateArray(0 To i) As Date
dateArray(UBound(dateArray)) = CDate(myDate)
i = i + 1
file = Dir
Wend
SortAr dateArray
MsgBox Replace(dateArray(UBound(dateArray)), "/", ".")
End Sub
Sub SortAr(arr() As Date)
Dim Temp As Date
Dim i As Long, j As Long
For j = 2 To UBound(arr)
Temp = arr(j)
For i = j - 1 To 1 Step -1
If (arr(i) <= Temp) Then GoTo 10
arr(i + 1) = arr(i)
Next i
i = 0
10: arr(i + 1) = Temp
Next j
End Sub
In the first macro change D:\test\ to the location of the directory that contains the files.
This macro is setup to give you the most recent date from the files that have the structure that you mentioned and it seems to be working just fine.
Currently, the macro outputs the most recent date in a message box so you can see the result and make sure it works; once it looks fine to you, just replace MsgBox (in the last line of the first macro) with something like mostRecentDate =
Once you are able to get the correct date value, just use that to create the reference to the new file.