Help with Macro to Call Excel File with Variable Names

Evening All,

Need help with a Macro to open a specific file based on today's date or more to the point week number.

I have a file which is named 'Daily Expectation Week ' (then the week number).

I need to have a macro to call the file for this week, one for last week, & one for the following week.

Can the macro see today's date, work out the week number like =weeknum() & use a -1 for previous week & +1 for the following week?

All help appreciated as always.

Cheers
John

Using WeekNum = DatePart("WW", Now()) before the standard call off of a file name, then adding " & WeekNum & " after the file name will give an addition to the file name being the week number.

So if 'now' = 6th April, then 'WeekNum' = 14. This would create the following link 'Workbooks.Open Filename:="R:\Factory\PLANNING\2013\Site Utilisation & Production Figures 2013\Perod 4\Daily Expectation Week 14.xlsx'

You can also add a variable to the 'WeekNum' by adding a '- 1' or '+ 1', this on the same date would return a 13 or a 15 at the end of the link.

WeekNum = DatePart("WW", Now())
Workbooks.Open Filename:="R:\Factory\PLANNING\2013\Site Utilisation & Production Figures 2013\Perod 4\Daily Expectation Week " & WeekNum & ".xlsm"
'or'
WeekNum = DatePart("WW", Now()) - 1
Workbooks.Open Filename:="R:\Factory\PLANNING\2013\Site Utilisation & Production Figures 2013\Perod 4\Daily Expectation Week " & WeekNum & ".xlsm"
'or'
WeekNum = DatePart("WW", Now()) + 1
Workbooks.Open Filename:="R:\Factory\PLANNING\2013\Site Utilisation & Production Figures 2013\Perod 4\Daily Expectation Week " & WeekNum & ".xlsm"

Hope this helps!!

John