Update Data Links ALWAYS freezes!

[cocojojo]

Hello,

I have been frustrated by this problem for some time- Whenever I try to update the source for data links in 2007 (Data>Connections>Edit Links>Change Source) excel ALWAYS freezes! I've tried this on other machines as well and the same thing happens...

useful info:
-I am trying to change the links for a lot (A1:CZ750) cells.
-I am linking to a new file on a shared network drive.
-The new file to which I am linking is virtually identical to the original

My workaround has been to change the file path in the actual cells and fill...but since this occurs on multiple machines/Excel installations I am thinking that I am doing something wrong.

Thanks for your help!

 

[xenou]

It may be if the file you are linking to is very large its just too much to handle - I've tried to avoid "heavy links". Opening the other workbook first, before creating the link, may speed it along.

 

[cocojojo]

It may be if the file you are linking to is very large its just too much to handle - I've tried to avoid "heavy links". Opening the other workbook first, before creating the link, may speed it along.

I just did a test with smaller files...works OK (although slower than it should be). I guess the large/complicated files im using are just too much (or I'm too impatient and abort the process before it's complete)

Thanks for your help

 

[tommyts]

The reason MAY have to do with your computer 'system resources'. One partial solution which helped me: Go to 'Task Manager'..select 'Processes'....select 'Excel.exe'...right click and select a higher priority level with 'Set Priority'. I believe this places Excel commands on a higher priority than other applications and reduces, if not eliminates, freezing due to heavy usage of your system resources.
Hope this helps!

 

[PBR]

If anyone's reading this and having the same problem I thought I'd share my experience.

I've been having this same problem, the PC apparently freezing and taking up to 20 hours to change the source of the link between the spreadsheet and another on a network drive.

Found after reading this that very simply, just having both spreadsheets open (ie the one containing the links and the one being linked to) hugely speeds up the process.

20 hours turned into less than 20 seconds!!

Hope this helps someone else out there who may be pulling their hair out in frustration as I was.

 

[TheGod1]

I was having LONG WAIT TIMES because of this.
PBR, your advice worked wonders. My file im linking would take over 30 mins (and hang). i tried this and it took approximately 1 minute instead.
Just had to log on to give you the like! :D
Thanks for the awesome advice!
First thing tomorrow at work I am going to fix my link!!

 

[vlkrn]

Registered just to say thanks to PBR. Thanks for this very very useful advice!