how to change link source

Andrew XJ · Feb 27th, 2002, 07:45 PM

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 · Feb 27th, 2002, 07:52 PM

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 · Feb 27th, 2002, 08:16 PM

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 · Feb 28th, 2002, 01:01 AM

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 · Feb 28th, 2002, 11:04 AM

Quote:

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.

Feb 27th, 2002, 08:16 PM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ? __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 27th, 2002, 07:45 PM	#1
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	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

Feb 27th, 2002, 07:52 PM	#2
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	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

Feb 28th, 2002, 01:01 AM	#4
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	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.