2003 to 2007 Excel upgrade issues.

[cecil]

We upgraded to Office 2007 and it is creating a mess. We have a complicated model, with files that roll up data, based on links to other files. The hierarchy is about 4 deep (file 1 links to and rolls up file2 which links to and rolls up file3 which links to and rolls up file 4: at the top level there is 1 file, at the bottom level about 50). These files likely were created with Excel 95. Last year they were run on Excel 2003, without a problem. I do not know wheter they were updated to 2003 file format or were run in compatibility mode.

Each year we create a new folder and copy the prior years model into it. Next we update the links. This year we saved as XLSM files after we made the copies. Now when we try to update the links two issues arrise. The links take forever to update. What used to take a few seconds now takes more than an hour. Some links will not update. Excel crashes with a "Microsoft Office Excel has stopped working" message.

I opened the XLSM files in Excel 2003 and updated the links just fine. However, if someone has a file open, Excel gives the message "'filename.xlsm' cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding." It should simply ask if I want to open read only or notify.

[HansV]

Welcome to Eileen's Lounge!

This is probably unavoidable. Each new version of Excel is much slower than the previous one; the "solution" is to get a faster computer. Links are a known problem - see External links may be updated in Excel 2007 when you open a workbook that was last saved in an earlier version of Excel.

I'm sorry I'm not able to give a more constructive reply!

[Jan Karel Pieterse]

What *may* help, is :
- Open the top level file
- Open the files it is linked to
- Save-as all SOURCE files to the xlsm (or xlsx if no macro is present) format, don't close any of them yet.
- Hit control+alt+shift+F9 to force Excel to do a full calculation, rebuilding the calculation tree.
- Now close the lower level files, leave the top level file open if it is the source of a higher level file.
- Repeat from first step, where your new top level file is the one "above" your current top level file.