Linking two workbooks

OK this is a three part tricky problem.

After reading some useful online information about linking Excel 2016 workbooks I decided to try it in an effort to ensure data consistency and reduce the amount of time it took to update nearly 100 workbooks with relevant foreign exchange rates data (copy/paste) that needed frequent updating.

Some of these workbooks are quite large with up to 30 worksheets.

The link process seems to work OK BUT with provisions.

1) My workbooks now take SIGNIFICANTLY longer to open, one in particular taking up to 30 minutes!!! Arghhh! WHY is this now the case?

2) I set Excel to always trust VBA etc. and after linking the first time then saving then re-opening the file I get the message that updates can't be performed at that time and to either update values or continue without it. I then went (as instructed) to the Data tab/Edit links and set the Startup Prompt notification to always open with no notification and update links then saved the file. Re-opening a second time and I STILL get that message! WHY? The procedure described was supposed to stop that from happening!

3) After linking, if I change the name of the source file but NOT its location, will the name change be propagated to all the linked workbooks or do I have to (labouriously) manually edit EVERY instance of the name? (IF this is so I can see a 365 day job looming and fraught with error possibilities if I miss some instances!)

I'd appreciate some comments and advise on this from a skilled and knowledgeable user or MVP.

Thanks in advance.

Good afternoon USAOz

...and welcome to the forum!!

1. If you have monster workbooks with monster formulae, then Excel is not going to work things out any quicker. It still has to open workbooks in the background to get information to pass to the master book. Granted, 30 minutes does seem like too long, but I don't know how much data / formulae is in these workbooks ...

2. Not really sure which message box you're describing here. Are the paths to all the workbooks all available? It sounds as though there is one or more dodgy paths in there. Check by going to Data > Edit Links and hit the Check Status button. This will indicate which ones can't be seen. If this doesn't work, start a new thread with a picture of the offending message.

3. From the Data > Edit Links tool you should be able change the source data paths and files, so it might still take a while, but it will be quicker, and using the Check Status button you can ensure you don't miss any.

HTH

DominicB

Thanks for the advise. However, editing the link in the Data tab option does NOT change the cell references! Arghhh!