how to change link source

[Andrew XJ]

This problem prompted to me for a long time.
I have several files which are referenced by one file. My problem is that, after i renamed the referenced files as a new set of files, i need to go to "Edit->Links" to change sources(since the referenced file names and path are changed) one by one.
I have tried to record Macro to change the link sources but it doesn't work. Could anyone help me? Thank in advance.

Here to Learn
XJ

 

[Andrew XJ]

Detailed Description:
A folder Data-0001 contains file Data-0001-Q1.xls, Data-0001-Q2.xls, Data-0001-Q3.xls, Data-0001-Q4.xls, Data-0001-Summary.xls.
Q1 through Q4 means quarter 1 through quarter 4. These four files are referenced by Data-0001-Summary.xls.
What i need to do is to make another copy of folder Data-0002 and change all "0001" to 0002", but the referenced files by Data-0002-Summary are still Data-0001-Q1,..., so i need to open Data-0001-Summary and go to "Edit->Links" to change all the referenced file name Data-0001-Q1,..., to Data-0002-Q2,...,. This action can be easily done manually. But I need to do this 200 times. That means i need folder from Data-0001 to Data-0200.
How can i use Macro to update the link source?
Thanks!!!

XJ

 

[Chris Davison]

Hi Andrew,

this does not answer your actual question as what would the macro be, but can you not create a new folder (not copy the existing one with all its contents) then open all 5 workbooks and "save as" with the new filenames into the new folder ?

Links will change automatically if the books are open.

Maybe you can use this as a last resort if nobody can come up with some code for you ?

 

[Andrew XJ]

Thank you!!!Chris. I posted this question a few days ago, but nobody answer it. In fact i feel your idea is still a feasible idea. Thank again.

 

[Chris Davison]

then open all 5 workbooks and "save as" with the new filenames into the new folder ?

I should have added, save your data files first, not the summary file. Renaming these will update the links in your summary files - you can literally see it happen before your eyes.