Links to closed files not opening.

[Mal Osborne]

I have a customer with a set of rather convoluted mish-mash of spreadsheets,
basically running an entire company. One spreadsheet (SSa) has a number
of links to 4 others (SSb,c,d). Everything works as expected if SSb,c,d are
open, & then SSa is opened, but if SSa is opened before SSb,c & d, cells in
SSa display #VALUE!. I have tried the same set of files on several
machines, running Excel 2000,XP & 2003, all act in the same manner. I have
tried to recreate the problem with a set of files linked in a similar way,
but in any case, the links were able to access data in closed files without
problem.

Any pointers on how to troubleshoot this problem would be greatly
appreciated!

Mal Osborne
MCSE MVP (not excel) Mensa

 

[Mike A]

I think the problem is either the way the links were created in the
destination spreadsheet, or the settings some of the workbook-specific
options.



First, in SSa.xls, go to Tools>Options>Calculation and make sure that
'Update remote references' is checked. This option is saved with the
workbook.

If that doesn't work, in SSa.xls, click Edit>Links... This will bring
up the Links dialog (surprisingly). Select one of the links, and
click change source. Navigate to the same file and click 'OK'. Repeat
for the other links. Make sure 'Automatic' updating is selected at
the bottom of the dialog for all of the links.

You may have to rebuild the links manually, which should be a last
resort. Check the formula in the linked cells in SSa.xls - it should
be something like:

=[SSb.xls]sheet1!A1

If these suggestions don't work, well, I have run out of Excel ideas,
but here is a creative workaround. Create a batch file that will open
the sheets in the right order. Create a new file in a text editor:

start SSd.xls
start SSc.xls
start SSb.xls
start SSa.xls

Now save it as SSa.bat. If they are using Windows 2000 or XP, the
command window will close after the last sheet opens. On
Windows95/98/ME, create a shortcut to the file and check the 'Close on
Exit' setting. When they use this shortcut, all the files will open,
with SSa.xls being the active file. Not the best solution, but it
will work till we can straighten out the links problem.


-Mike

I have a customer with a set of rather convoluted mish-mash of spreadsheets,
basically running an entire company. One spreadsheet (SSa) has a number
of links to 4 others (SSb,c,d). Everything works as expected if SSb,c,d are
open, & then SSa is opened, but if SSa is opened before SSb,c & d, cells in
SSa display #VALUE!. I have tried the same set of files on several
machines, running Excel 2000,XP & 2003, all act in the same manner. I have
tried to recreate the problem with a set of files linked in a similar way,
but in any case, the links were able to access data in closed files without
problem.

Any pointers on how to troubleshoot this problem would be greatly
appreciated!

Mal Osborne
MCSE MVP (not excel) Mensa

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers

 

[Dave Peterson]

I've seen some posts that say that if the workbooks are old (created in an
earlier version than what you're running now), then excel will want to
recalculate the workbook on opening.

If you answer no to the links, then you'll get that #value! error.

Any chance this describes your problem.

If it does, then just Edit|Links|Update Links.

====
And I've gotten #ref! errors with links to closed workbooks--if the links were
embedded in =indirect()'s. But that doesn't seem to quite fit your scenario.