External links may update when you open a file that was last saved in a previous version of Excel (327006)
The information in this article applies to:
- Microsoft Office Excel 2003
- Microsoft Excel 2002
- Microsoft Excel 2000
This article was previously published under Q327006 SYMPTOMSWhen you open a workbook that contains external links in
Microsoft Excel 2000 or a later version, you are prompted to update the links. If you
click No, you may experience one or more of the following
symptoms:
- The links may be updated anyway.
- Cells that contain external links that are not found return #REF!, #Value!, or #NAME! errors. Additionally, because of the link value being an error, functions that try to calculate this value may return other errors.
CAUSEThis problem occurs when the workbook that you are opening was last saved in a version of Microsoft Excel that is earlier than the version that you are currently using to open the file. For example, this problem occurs when you are opening a workbook in Excel 2000 that was last saved in Microsoft Excel 97. This problem occurs because, starting with Excel
2000, Excel forces a complete recalculation of all workbooks that are opened that were previously saved in an earlier version of Excel, regardless of the
link-update status. To fully recalculate the workbook, Excel forces updates of all external references, even if you choose not to update when you are prompted to. This process occurs because Excel is updating the workbook
calculation chain to the current version. If the external link sources are not
available, Excel cannot calculate correctly and returns #REF! errors. This is
also true of DDE links that are not available during the recalculation
process.WORKAROUND To work around this problem, use one of the following
methods. Make sure that external link sources are available before you open the fileIf you open the file and have errors in external link formulas but you have not saved the file yet, follow these steps: - Close the workbook without saving to leave the workbook unchanged.
- For each different external link source in the file that you want to open, confirm that the source file is available at the path that is specified in the link formula. If any link sources are no longer available, you must change the link formula to point to an alternative source or remove the link formula permanently to break the link. Follow the steps in the next section to edit or to remove links.
- After you confirm that all the link sources are available at their defined locations, open the linked file in Excel and let Excel update external links to the file when it is prompted to.
- When you open the linked file and confirmed that all external links have updated successfully and that the workbook has been successfully recalculated in the current Excel version, save the file. The file should now open and update links as expected in the current version of Excel.
Update or remove linksIf you already saved the file with errors in the external link formulas, or if the link source files are no longer available, you must locate the original linked source or alternative linked sources and then restore
the links to these sources. To look at the external link sources and to restore or remove
any broken links, follow these steps:
- To temporarily prevent the recalculation of files last saved in an earlier version of Excel so that you can update or remove external links, set
the calculation environment to Manual. To temporarily set the calculation mode
to Manual, follow these steps:
- Close all the workbooks that are open.
- Create a new workbook.
- On the Tools menu, click
Options.
- Click the Calculation tab, and then click
Manual.
- Click to clear the Recalculate before save
check box, and then click OK.
- Open your saved workbook.
- On the Edit menu, click
Links.
- Click Check Status to update the status
for all the links in the list. Wait for the status of all links to be
updated.
- Examine the status in the Status column,
click the link, and then take one of the following actions:
- OK. No action is required. The link is
working and is current.
- Unknown. Click Check
Status to update the status for all links in the list.
- Not applicable. The link uses OLE or
Dynamic Data Exchange (DDE). Microsoft Excel cannot check the status of these
types of links.
- Error: Source not found. Click
Change Source, and then select the appropriate workbook for the link.
- Error: Worksheet not found. Click
Change Source, and then select the appropriate worksheet in the appropriate file. The source
may have been moved or renamed.
- Warning: Values not updated. Click
Update Values. The link was not updated when the workbook was
opened.
- Warning: Source not recalculated.
Click Open Source, and then press F9 to calculate the
workbook. The workbook may be set to manual calculation. To set to automatic
calculation, click Options on the Tools menu,
and then click Automatic on the Calculation
tab.
- Error: Undefined or non-rectangular
name. Some names cannot be resolved until you open the source
workbook. Click Open Source, switch back to the destination
workbook, and then click Check Status. If this does not
resolve the issue, make sure that the name is not misspelled or missing. Switch
to the source workbook, point to Name on the
Insert menu, click Define, and then look for
the name.
- Warning: Open source to update values.
Click Open Source. The link cannot be updated until you open
the source.
- Source is open. The source is open. No
action is required unless you receive worksheet errors.
- Values updated from file name. No
action is required. The values have been updated.
- Error: Status indeterminate. Excel
cannot determine the status of the link. The source may contain no worksheets,
or may be saved in an unsupported file format. Click Update
Values.
- After you resolve all link references, reset calculation to automatic so that Excel can fully recalculate the workbook in the new version. To do this, follow these steps:
- On the Tools menu, click
Options.
- Click the Calculation tab, and then click
Automatic.
- Click OK.
Excel should calculate the file at this point. If the calculation is successful, save the file. The file should now open and update links as expected in the current version of Excel.
For more information about how the calculation environment is
determined, click the following article number to view the article in the Microsoft Knowledge Base:
214395
Description of how Excel
determines the current mode of calculation
Permanently remove the link formula and replace it with the value If you do not need the formulas that refer to external links,
copy these formulas, and then paste the values only in the target
cells. Note When you replace a formula with its value, Excel permanently
removes the formula. To copy the formulas and paste the values,
follow these steps:
- Open the workbook in the version of Excel where it was last saved. When you are prompted to update the links, click No. Because the file was last saved in this version, links are not forced to update, and calculation can occur with the last known value of the link.
- Right-click the cell or range of cells that contains the formula that refers
to an external link, and then click Copy.
- Right-click the same cell, and then click Paste
Special.
- Under Paste in the Paste
Special dialog box, click Values, and then click
OK.
After you remove all the unwanted links in this manner, save the file. You can then open the file in the new Excel version without updating those links because they no longer exist. STATUSThis
behavior is by design.
Modification Type: | Major | Last Reviewed: | 5/12/2006 |
---|
Keywords: | kbopenfile kbxlslink kberrmsg kbprb KB327006 |
---|
|