import worksheets from changing workbooks name

[alienscript]

Dear VBA Excel expert,

I am hitting wall as a part of my office work data reports automation. I hope I could get some help from this great forum. The workbooks are stored in C:\WeeklyData\ in filename format as WEEKLY DATAFILE YYYY-MM-DD.xls Sometimes there are several different weekly workbooks in the folder. The file with the lastModified Date may not be the latest or most updated file because someone could have opened and saved an old file before the procedure is executed.

In my examples I used the method to open the most recent file which is by far I could achieve. I want the procedure to be able to open the workbook with the latest date as part of the date format in the filenames. I am desperate for some help and hope for some kind answers here. With great gratitude and Thousands of thanks.

I am using Xls 2003, Windows XP and attached the work examples

[Charlize]

I believe this one will open the workbook based on the name of the workbook. I'll leave the importing to you.Sub test_weekly_files()
Dim mypath As String
Dim myfile As String
Dim vdate As Date
Dim vday As String
Dim vmonth As String
Dim vyear As String
vdate = DateSerial(2007, 1, 1)
mypath = ActiveWorkbook.Path & "\WEEKLY DATAFILE*.xls"
myfile = Dir(mypath)
Do While myfile <> ""
If DateSerial(Right(Split(myfile, "-")(0), 4), Split(myfile, "-")(1), _
Left(Split(myfile, "-")(2), 2)) > vdate Then
vyear = Right(Split(myfile, "-")(0), 4)
vmonth = Split(myfile, "-")(1)
vday = Left(Split(myfile, "-")(2), 2)
vdate = DateSerial(vyear, vmonth, vday)
End If
myfile = Dir
Loop
If vyear <> vbNullString Then
Workbooks.Open (ActiveWorkbook.Path & "\WEEKLY DATAFILE " & vyear & "-" & _
vmonth & "-" & vday)
Else
MsgBox "No weekly data was found !", vbInformation
End If
End Sub

[alienscript]

Dear Charlize, it works perfectly and thank you for all the help :-)