Hi all.
I have a workbook that I'd like to link to 30 to 35 other workbooks so that
it will update each time it's opened, to the most recent versions of those 30
to 35 workbooks.
I.e., the files will typically have the same names as the "old" ones, and
will be replaced every few weeks.
So, to see if I can clarify this....
Workbook A presently contains worksheets from workbooks WB1 through WB35.
Each time Workbook A is opened, with the linked worksheets, it gives a
message stating that worksheets within the workbook are linked to other
workbooks, and asks if the user wants to update those links.
However, at least a couple of the workbooks WB1 through WB35 have been
changed/modified since the last time WorkbookA was opened. In fact, it would
be a different file than it was before-- e.g. a few different people have
worked on it, changes have been made, and it now resides, as a replacement of
one of the original workbooks WB1 through WB35. E.g. WB1 could now be
WB1-August2007, to show that it's been changed since last being worked on.
My want is to have the macro, or link tool go through the specified
directory that we store our "published" workbooks, and look for the
replacement workbook, and update the data that's been changed from the old to
the new.
1- how would this be accomplished?

I suppose that I should state that the ultimate goal will be to place all of
the data into our Access database. And hopefully have it update
"automatically" as well.

My initial thoughts were to come up with a macro that will check if the last
modified date is earlier, or later than the last time the workbookA was
saved. If it was later open the "source" workbooks, WB1.... WB35, look at the
information on the desired (specified by one of two "catch" words) 'A' or 'B'
worksheet (then look for a specific phrase that is on each worksheet (123),
within its specified workbook- WB1....WB35) replace that specific worksheet
with the newest version, and then rename the replacement worksheet to the
correct name- determined by the "catch" phrase/word mentioned above.
So, it seems to me that stating the source directory, on a specific server
would be easily enough done. Something similar to:
Workbooks.Open "S:\Assignments Final\TRC\..."

I'd then need to have a "search" component for the workbook, and then one
for the worksheet, and then for a specific phrase, or word on the chosen
worksheet.
Once it identifies the worksheet correctly, it'd need to copy the worksheet
into the workbookA. Once the copy function is complete, I'd need to have it
rename the worksheet to the specified word- 123, 122, 124, etc.....
If more specific information is needed, please ask.
In advance, as always, thank you for your time, and willingness to help.
Best.