Excel 2007 : pull up data depending on specific date in cell

Hi guys,

I have a spreadsheet that needs to get data off another spreadsheet but the problem is a new report comes out daily with the same prefix filename everyday but the only thing that changed in the filename is the date in the end.

ex: SignOn-Agents-UTOPIA - 2012-04-18 - Mercredi1.xlsx

How can I pull up the data depending on the date i put in a specific cell?

Hi
Try this macro
enter the file directory where it says "MyPath = "
it assumes your default language in Excel is French - Format(Date, "dddd") comes up with "Tuesday" for me, but hopefully you'll get "Mercredi"

Sub file_open_todays()
Dim MyPath, MyFileName
MyPath = "C:\Mydocuments\" 'enter file directory here, including concluding "\"
MyFileName = "SignOn-Agents-UTOPIA - " & Format(Date, "yyyy-mm-dd") & " - " & Format(Date, "dddd") & "1.xlsx"
Workbooks.Open Filename:=MyPath & MyFileName
End Sub

Hi, thank you for your reply

Unfortunatly it did not work. As you said it is returning tuesday instead of Mardi..

Is there a string of code I can add for it to convert it to french format?

Hi
I assume that you want a the number 1 after the french word for the day, as in your original post
If so, try this:

Sub file_open_todays()
Dim MyPath, MyFileName, MyDays, MyDay As String, MyDayNum As Long
MyDays = Array("dimanche", "lundi", "mardi", "mercredi", "jeudi", "vendredi", "samedi")
MyDayNum = Application.WorksheetFunction.Weekday(Date) - 1
MyDay = MyDays(MyDayNum)

MyPath = "C:\Mydocuments\" 'enter file directory here, including concluding "\"
MyFileName = "SignOn-Agents-UTOPIA - " & Format(Date, "yyyy-mm-dd") & " - " & MyDay & "1.xlsx"
MsgBox MyFileName
Workbooks.Open Filename:=MyPath & MyFileName
End Sub