Problem changing external file paths in formulas

[Edward_123]

I have an Excel file with a lot of fomulas some usinging extrenal excel files. After I moved the files to a new location, for some reason these links didn't change automatically , so I thought I can use Edit link dialogbox and then by using change Source, easily change the links and give a new location to all the links , but when I use change source in edit link dialogbox , and enter new path, I repeatedly get open file dialogbox asking for new ptha instead of just updating the source file path. I've tried to use find \replace for changing the path to external files in my fomulas , but no luck so far. Any suggestions how I can fix this?
Thanks

 

[Arul.rajesh]

You cannot move the files while they are linked to another workbook. Instead, while keeping the file with the links Open, open the workbook to which the links refer to and do a "saveas" to a new location, then save the file with the links. Close the file with links then close the source file.

if you are comfortable with VBA you can use the

workbooks("The filename which contains your links").linksources(xlExcelLinks)

to assign all the links to an array. Then update them with "changelink" method of the workbook.

 

[Edward_123]

Thank you
Unfortunately , that method didn’t work , I opened all 5 files and saveAs to new location , and then saveAs the master file , but all linked are referenced to the old location.
I’m interested in VBA solution you mentioned. Lets say I have a file (I’ll call it master file) which has thousands links to 4 other excel files. And formulas like the following
='C:\Users\ab\Desktop\ [WB1.xlsx]Financial Summary'!$E$27 + ' C:\Users\ab\Desktop\ [WB2.xlsx]Financial Summary'!$E$27 + ' C:\Users\ab\Desktop\ [WB3.xlsx]Financial Summary'!$E$27 +' C:\Users\ab\Desktop\ [WB4.xlsx]Financial Summary'!$E$27
How can I use VBA to change the paths to a different folder like C:\test\
Thank you