I'll jump right into it:
We run a number of weekly reports that export to excel very nicely. My boss has asked for a workbook he can simply open and print that contains charts that show not only the weekly reports but a summary of all reports over the course of a calendar year.

The weekly report workbook calls data from 6 different exported documents to populate its charts. Can I somehow tell excel to look for workbooks with a specific name (in this case, the date range of the weekly report) and draw chart data from it if it exists, but to skip that file if it does not exist?

To put it another way:

Can I have a series of charts that will display weekly reports from all of January through June 14th at the end of this week AND will display data from January through October at the end of October as the new report files are saved?

The best solution I have come up with so far is to
A) Create each of the 52 weekly report file in advance and populate it with null data (zeros in all columns), then have our report generation program save over each of these files at the end of each week.

B) Build 52 weeks of data points onto each table in the final report workbook and have each chart call data from the dummy files. This data will be replaced with actual data each week, so each of these charts (line charts where X = Weekly progress and Y = numerical values) will show data up to the current week and no data for future weeks.

Am I reinventing the wheel here? Please let me know if I can give any more specific information on how I am setting this up.

Thanks in advance!

-Schwavan