Link to non-existent files?

[Ray]

Good morning ..... or afternoon, for some of you -

Let's see how well I can describe my problem:
Let's say that I have 10 stores and each store reports their sales
figures in Excel, with a separate file for each week. So, at the end
of the year, each store would have 52 files ... but at any point during
the year, they of course have less than this amount.

I have a Summary WB with a sheet for each store, with a separate row
for each week. So, A2:A54 contain my week numbers (Row 1 contains
Headers). There's also a Totals sheet with sums up the total store
sales for each week.

Currently, someone goes in each week and manually copies down the
formulas on each tab to update the links to pull in last week's data,
changing the file name to the proper week #. This has worked 'OK' but
it's a bit manual for my liking ...

My first thought was to 'pre-build' the links to the
not-yet-in-existence weekly files, and then just Update_Links (via VBA)
to pull in the most recent data. HOWEVER, doing this creates alot of
'errors', as Excel asks where to find the non-existent files! And with
all of the potentially non-existent week/store combinations, this isn't
efficient.

SO .... my question: is it possible to build the links so that Excel
will first look to see if the file exists? If it does exist, the data
would update ... if not, it would just be ignored.

Assume that the path would be G:\\Server\Folder\[File]Sheet!

TIA,
Ray
XL2002 on XP

 

[Dave Peterson]

You could build 52*10=520 dummy workbooks that are named correctly. Then your
links could return whatever is in those dummy workbooks (0's???).

Or since you were going to use VBA to update the links, why not just have a
macro create the formulas -- but only if the workbook exists.

Good morning ..... or afternoon, for some of you -

Let's see how well I can describe my problem:
Let's say that I have 10 stores and each store reports their sales
figures in Excel, with a separate file for each week. So, at the end
of the year, each store would have 52 files ... but at any point during
the year, they of course have less than this amount.

I have a Summary WB with a sheet for each store, with a separate row
for each week. So, A2:A54 contain my week numbers (Row 1 contains
Headers). There's also a Totals sheet with sums up the total store
sales for each week.

Currently, someone goes in each week and manually copies down the
formulas on each tab to update the links to pull in last week's data,
changing the file name to the proper week #. This has worked 'OK' but
it's a bit manual for my liking ...

My first thought was to 'pre-build' the links to the
not-yet-in-existence weekly files, and then just Update_Links (via VBA)
to pull in the most recent data. HOWEVER, doing this creates alot of
'errors', as Excel asks where to find the non-existent files! And with
all of the potentially non-existent week/store combinations, this isn't
efficient.

SO .... my question: is it possible to build the links so that Excel
will first look to see if the file exists? If it does exist, the data
would update ... if not, it would just be ignored.

Assume that the path would be G:\\Server\Folder\[File]Sheet!

TIA,
Ray
XL2002 on XP