Update links without opening other files

[maryj]

Client is using Excel 03/WinXP. She has a Summary file. She enters a value
into the Summary sheet, which then populates a cell in File A. File A then
does a calculation on that value. The Summary sheet should then display that
calculation.

If both the Summary file and File A are open, everthing updates. However,
she has the Summary file linked to about 60 other files. She was hoping this
would work so that she would not have to go into all 60 files to make the
changes. Calculation is set to Automatic and have tried the Update Links
button in the Edit, Links dialog box but none of those work. Would there
maybe be some code that would update the Summary sheet without having to open
the other files?

 

[Stephen C]

Try this Tools-Options-Calculation and change the calculation manual to
automatic

 

[Tom Ogilvy]

You can't do what you describe with links when the other workbooks are
closed. (regardless of the calculation setting as suggested by Stephen C).

Links read data, they don't write data.

Excel provides the code/intelligence to interpret the formulas in the
workbook and peform the appropriate action. A closed workbook neither
updates links (to get the value you changed) nor performs calculations on
that data. Excel does that. You need to open the workbook(s) to achieve
that reaction.

 

[maryj]

Thanks, but as I said, Calc is already at Automatic.

 

[Stephen C]

I miss read your post.

You could create some code to open all 60 files one at a time then save and
close them, this would update the files with the new value do the calculation
and then update the summary with the calculation.

You could link the code to a worksheet change event so it updates
automatically when you input the value.