Assuming you know the year and month, maybe you could start with something
like this:

FileName = Dir$("200410*.XLS")
LastFile = ""
Do While Len(FileName) 0
If FileName LastFile Then
LastFile = FileName
End If
FileName = Dir$()
Loop

Or, if the files are written on the same day of the week, you should be able
to construct a list of the possible dates. If you create that list in
descending order, you can look for the files with the Dir$ command until you
find one. Assuming they are written on the 28th, 21st, 14th, and 7th

D = DateSerial(2004,10,28)
Do
If Len(Dir$(Format$(D, "yyyymmdd") & ".XLS")) Then Exit Do
D = D - 7
Loop

FileName = Format$(D, "yyyymmdd") & ".XLS"




On Thu, 4 Nov 2004 18:55:01 -0800, "Todd"
wrote:

Hi All,

I am trying to write a macro that opens the newest file in a directory,
currently the files name contains the date in YYYYMMDD format and is run on a
weekly basis

Thanks