One way:
Option Explicit
Sub testme01()
Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim NameToKeep As String
Dim wkbk As Workbook
'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If
'get the list of files
fCtr = 0
Do While myFile <> ""
If LCase(myFile) Like "######01.xls" Then
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
End If
myFile = Dir()
Loop
NameToKeep = ""
If fCtr > 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
If myFiles(fCtr) > NameToKeep Then
NameToKeep = myFiles(fCtr)
End If
Next fCtr
Else
MsgBox "No Files matching pattern"
Exit Sub
End If
If NameToKeep = "" Then
' 'shouldn't happen
MsgBox "something bad happened"
Else
' MsgBox NameToKeep & vbLf & MonthName(Mid(NameToKeep, 5, 2), False)
MsgBox Format(DateSerial(2004, Mid(NameToKeep, 5, 2), 1), "MMMM")
' Open it???
' Set wkbk = Workbooks.Open(Filename:=myPath & NameToKeep)
End If
End Sub
MonthName was added in xl2002. But you could use format to get the name of the
month.
