Microsoft KB Archive/925893

Knowledge Base

External links may be updated in Excel 2007 when you open a workbook that was last saved in an earlier version of Excel

Article ID: 925893

Article Last Modified on 10/15/2007

---

APPLIES TO

• Microsoft Office Excel 2007

---

For a Microsoft Office Excel 2003, Microsoft Excel 2002, or Microsoft Excel 2000 version of this article, see 327006.

SYMPTOMS

When you open a workbook that contains external links in Microsoft Office Excel 2007 or in a later version of Microsoft Excel, you are prompted to update the links. If you click Option, and then click Help protect me from unknown content (recommended), you may experience one or more of the following symptoms:

• The links in the workbook may be updated anyway.
• Cells that contain external links to other workbooks that cannot be found will display one of the following errors:
  • #REF!
  • #Value!
  • #NAME!
  Additionally, because the link value is an error, functions that try to calculate this value may return other errors.

CAUSE

This problem occurs if the workbook that you open was last saved in a version of Excel that is earlier than the version that you are currently using to open the workbook. This problem occurs because Excel 2007 forces a complete recalculation of all open workbooks that were previously saved in an earlier version of Excel, regardless of the link update status. To fully recalculate a workbook, Excel 2007 forces updates of all external references. This behavior occurs even after you decide not to update those links when you are prompted. Excel 2007 updates the workbook calculation chain to the current version of Excel. If the external link sources are unavailable, Excel 2007 cannot calculate correctly. Therefore, Excel 2007 returns #REF! errors. This is also true of DDE links that are unavailable 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 workbook

If you have errors in external link formulas when you open the workbook, but you have not yet saved the workbook, follow these steps:

1. Do not save the workbook. Instead, close the workbook without saving it. This will undo any changes that were made to the workbook.
2. For each different external link source in the workbook 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, change the link formula to point to an alternative source. Alternatively, remove the link formula permanently to break the link. Follow the steps in the "Update or remove links" section to edit links or to remove links.
3. After you confirm that all the link sources are available at their defined locations, open the linked workbook that experienced the problem in Excel 2007. Let Excel 2007 update all external links within the workbook when you are prompted.
4. When you open the linked workbook and confirm that all external links have updated successfully and that the workbook has been successfully recalculated in the current version of Excel 2007, save the workbook. It should now open and update links as expected in the current version of Excel 2007.

Update or remove links

If you already saved the workbook that has errors in the external link formulas, if the link source file has moved, or if the link source files are no longer available, locate the original linked source. Alternatively, find an alternative source file. Then, modify the links to these sources. To examine the external link sources and to restore or remove any broken links, follow these steps:

1. To temporarily prevent the recalculation of files that were 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:
   1. Close all workbooks.
   2. Create a new workbook.
   3. Click the Microsoft Office Button, and then click Excel Options.
   4. On the Formulas tab, click Manual under Calculation options, and then click OK.
   5. Open the saved workbook.
2. On the Data tab, click Edit Links in the Connections group.
   
   Each link will list the file name of the source workbook to which this workbook is linked. If you click the link, the original file path location of the source workbook appears under the list in the Location label.
3. Click Check Status to update the status for all the links in the list. Wait for the status of all links to be updated.
4. Examine the status in the Status column, click the link, and then perform one of the following actions:
   • If the status is OK, no action is required. The link is working and is current.
   • If the status is Unknown, click Check Status to update the status for all links in the list.
   • If the status is Not applicable, the link uses OLE or Dynamic Data Exchange (DDE). Excel 2007 cannot check the status of these types of links.
   • If the status is Error: Source not found, click Change Source, and then select the appropriate workbook for the link.
   • If the status is 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.
   • If the status is Warning: Values not updated, click Update Values. The link was not updated when the workbook was opened.
   • If the status is Warning: Source not recalculated, click Open Source, and then press F9 to calculate the workbook. The workbook may be set to manual calculation in the source file. To set the workbook to automatic calculation, click the Microsoft Office Button, and then click Excel Options. On the Formulas tab, click Automatic under Calculation options.
   • If the status is 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 missing or misspelled. Switch to the source workbook, click the Formulas tab, click Define Name, and then look for the name.
   • If the status is Warning: Open source to update values, click Open Source. The link cannot be updated until you open the source.
   • If the status is Source is open, the source is open. No action is required unless you receive worksheet errors.
   • If the status is Values updated from file name, no action is required. The values have been updated.
   • If the status is Error: Status indeterminate, Excel 2007 cannot determine the status of the link. The source may contain no worksheets. Alternatively, the source may be saved in an unsupported file format. Click Update Values.
5. After you resolve all link references, reset calculation to automatic so that Excel 2007 can fully recalculate the workbook in the new version of the workbook. To do this, follow these steps:
   1. Click the Microsoft Office Button, and then click Excel Options.
   2. On the Formulas tab, click Automatic under Calculation options, and then click OK.
   Excel 2007 should now calculate the workbook. If the calculation is successful, save the workbook. The workbook should now open and update links as expected in the current version of Excel 2007.

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 then 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 2007 permanently removes the formula.

To copy the formulas and paste the values, follow these steps:

1. Open the workbook in the version of Excel in which the workbook was last saved. When you are prompted to update the links, click No. Because the file was last saved in this version of Excel, links are not forced to update. Calculation can occur with the last known value of the link.
2. Right-click the cell or range of cells that contain the formula that refers to an external link, and then click Copy.
3. Right-click the same cell or range of cells, and then click Paste Special.
4. In the Paste Special dialog box, click Values under Paste, and then click OK.

After you remove all the unwanted links in this manner, save the workbook. You can then open the workbook in Excel 2007 without updating those links. This behavior occurs because the links no longer exist.

Additional query words: prb XL2002 XL2007 update links XL

Keywords: kberrmsg kbtshoot kbdde kbprb kbxlslink kbexpertisebeginner KB925893

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.