How to reset the Update Remote References option in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q110006
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
When you open a workbook in Microsoft Excel 2000, in Microsoft Excel 2002, or in Microsoft Office Excel 2003, the Update Remote Referencescalculation setting for the workbook is usually selected. The exception tothis rule occurs when a workbook has been linked to an external data source,and when you do not allow Excel to update links to the external datasource.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

The Update Remote References calculation setting determines whether Excel updates formulas that include references to external datasources, such as Microsoft Word for Windows documents or Microsoft Accessdatabases.

Note Excel workbooks and documents are notexternal data sources.

When you open a workbook, the Update Remote References setting is setaccording to the following table.
Version of ExcelConditionUpdate Remote References check box
Excel 2000The workbook is linked to external data.

You click No when you receive the following prompt:

"The workbook you opened contains automatic links to information in another workbook. Do you want to update this workbook with changes made to the other workbook?"
Cleared
Excel 2000The workbook is linked to external data.

You click Yes when you receive the following prompt:

"The workbook you opened contains automatic links to information in another workbook. Do you want to update this workbook with changes made to the other workbook?"
Selected
Excel 2002The workbook is linked to external data.

You click Don't Update when you receive the following prompt:

"This workbook contains links to other data sources. If you update the links, Excel attempts to retrieve the latest data. If you don't update, Excel uses the previous information."
Cleared
Excel 2002The workbook is linked to external data.

You click Update when you receive the following prompt:

"This workbook contains links to other data sources. If you update the links, Excel attempts to retrieve the latest data. If you don't update, Excel uses the previous information."
Selected
Excel 2003The workbook is linked to external data.

You select Don't Update when you receive the following prompt:

"This workbook contains links to other data sources. If you update the links, Excel will attempt to retrieve the latest data. If you don't update the links, Excel will use the previous information."
Cleared
Excel 2003The workbook is linked to external data.

You click Update when you receive the following prompt:

"This workbook contains links to other data sources. If you update the links, Excel will attempt to retrieve the latest data. If you don't update the links, Excel will use the previous information."
Selected
To clear the Update Remote References setting in the active workbook, follow these steps:
  1. On the Tools menu, click Options or Preferences.
  2. Click the Calculation tab.
  3. Click to clear the Update Remote References check box.
  4. Click OK.

Sample Visual Basic procedure

The following Visual Basic code example assumes that you have a file that is calledTEST.XLS that is located in the C:\EXCEL directory. The example opens theTEST.XLS file and then clears (turns off) the Update Remote Referencescheck box. As the Sub procedure opens the file, it also preventsExcel from updating any of the workbook's external or remote references.
   Sub Example()       ' The zero after updateLinks indicates that neither external nor       ' remote references should be updated when the file is opened.       Workbooks.Open fileName:="C:\EXCEL\TEST.XLS", updateLinks:=0       ' Turn off the Update Remote References setting for the workbook.       ActiveWorkbook.UpdateRemoteReferences = False   End Sub				
Note The Update Remote References check box is not a universalsetting. When two workbooks are open, one workbook might have its Update RemoteReferences setting selected whereas the check box iscleared in the other workbook. However, when you open a workbook, its Update Remote Referencescheck box will be selected, no matter how it was saved, unless it containslinks to an external data source.
8.00 XL97 XL98 XL7 XL5 calc XL XL2003 XL2002 XL2000
Properties

Article ID: 110006 - Last Review: 12/04/2015 09:56:20 - Revision: 3.0

Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 98 for Macintosh

  • kbnosurvey kbarchive kbdtacode kbhowto kbprogramming KB110006
Feedback