Auto Update of Cells linked to other workbook - Excel 1

[Luther1978]

I have created a workbook in Excel 97, and have 3 sheets within it that take values from another set of workbooks.

I have linked all the workbooks to their correct file locations and on opening the parent workbook excel asks me if I want to update the information. OK I say. None of the cells containig references to the child workbooks seem to update. However I have found that if I manually open all the child workbooks and close them again the parent workbook gets populated with the correct data.

Why is this, and how do I automate the update?

Many Thanks

Martin King

 

[Hasit]

When you link multiple files together in the way you have, Excel needs to have all the files open to which you are linked.

When you open the files you are linked to and then close them, the parent file has the opportunity to update as requested.

The only way out of this is to open all the files, and then save them as a workspace (.xlw). If you click on FILE, SAVE AS WORKSPACE, then on opening up the workspace, all the files you need, including the parent and linked files will automatically open.

 

[GlennUK]

If you have linked workbooks of the same Excel version type, then you update should be automatic ( on saying OK for update on opening ).

Check that the child workbooks have been saved as Excel version 97, which of course so should the parent be as well.

Glenn.

 

[Luther1978]

Thanks Guys.

That's an Interesting Point Glenn, The child workbooks have been produced by access as result of a query I've ran. There are about 30 in total.

On editing in Excel and saving the changes Excel asked if I wanted to update the files to the newer version of Excel, which I assumed to be Excel 97. I clicked "yes" to Excel doing this, so all the workbooks should now be of Excel 97 type. My only guess here is that an update to 97 is not 100% complete?

Martin

 

[GlennUK]

Well, it sounds like your child workbooks are the right version. How about the parent? Have you checked that it's the correct version, and that Calculation is not set to Manual?

If these don't help, then I don't know what's causing your problems.

Glenn.

 

[Luther1978]

Calculation not set to Manual???

How do I check this?

 

[Luther1978]

Under the Edit Menu there is a Link option, All the links to the correct files are set to Manual.

 

[Nikita6003]

Luther, check using TOOLS > OPTIONS > CALCULATION

Nice one, BTW, Glenn about the different xl versions. Didn't realise that one ... star from me

Cheers
Nikki

 

[GlennUK]

Luther1978,

did you get this working? If not, can you change the links to be Automatic instead of Manual?

Cheers, Glenn.

 

[Luther1978]

Glenn, The links are set to be Automatic. Calculation is also set to be automatic.

arrrrggghhh.