automatic update of links has been disabled - Excell 2007

faolchu
faolchu used Ask the Experts™
on
Guys

I have a user that has a bunch of excel documents that all contain links to each other. from teh looks of it the excel documents are 2003 format but the user is using excel 2007.

Anyone know how to disable this permenantly and just have the documents update.  When I go in to check the links it states that the source can not be found even though the source is just another spreadsheet that I can open just fine.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Go to Office Button>Excel Options>Trust Center>Trust Center Settings>External Content
There you will find your options.

Author

Commented:
already set those to Enable Data Connections and Enable Automatc update of links.

I would assume that wouldn't need to be done on all spreadsheets as this would be a sertting for Excel and not eth woorkbook right
That's Correct.  So I'm not sure what the issue is?
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Author

Commented:
that's what has me stumped too.


when they now open the files they get an option to Continue or Edit Link. one of teh options here is to check teh source or sometihng, and in some instances it shows that they can't connect to the source even though i can manually open these work books.

I may just have to go down teh path of manually verifying the links within this window.
Commented:
sorry forgot to add this

http://support.microsoft.com/kb/925893/en-us 

Looks like it contains teh steps to manually updating the links. Well there goes my holiday weekend.
if the cell's are only referencing the workbook names instead of full paths then it will prompt that link is not found.
if the book isn't open I should say.

Author

Commented:
@MWGainsJR

ok that makes sense. I'll go and edit teh links and see if its full paths or just file name slisted

Author

Commented:
ok it had teh file name only so i selected the option where you recreate the link and then saved the document but came up with the same problem.

One thing i did notice is that the file it's linking to has a ludicrously long name. something liek 30 - 40 charachers not including the path. Could it be that?
Try typing out the full path in the formaula bar.
Also, workbooks with a space (" ") in the name require brackets ([ ]) around it.
A link would look like this:
='C:\[work book name.xlsx]sheetname'!$A$1

Author

Commented:
brackets are around the paths that have spaces.

the cause of the problem is related to the documents being in 97-2003 format. the workaround to recreate the links didn't work in my case. so the only "solution" I have until everyone that recieves copies of teh files upgrades to 2007 or instals the file converter tool by MS  is to have all workbooks linked to open, which isn't really a solution.  either that or downgrade my site to 2003.
Sounds about right to me.

Author

Commented:
added my own comment as partial solution because it includes some workaround steps to recreate links etc. also provided me teh info to keep other sheets open so that links update.

Commented:
I am looking at this thread but I do not see the solution really from the initial question. I have the exact issue. Essentially, Excel forces the recalculation when opening an older version. But this wreaks havoc to my users. In 2003, it used to prompt them to "Update Links" and in 2007 it simply will not. I have checked the boxes that say to prompt to update external links but nothing happens, it STILL tries to open the document and check all the links.

We have thousands of documents like this and is causing the users a lot of extra steps.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial