Update remote references check box is usually selected in Excel 2003 and in earlier versions of Excel

View products that this article applies to.

This article was previously published under Q213836

SUMMARY

When you open a workbook file (.xls) in Microsoft Office Excel 2003 and earlier versions of Excel, the Update remote references check box for the workbook is usually selected. The exception to this rule occurs when a workbook has been linked to an external data source and you do not allow Excel to update links to the external data source.

Back to the top

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 check box setting determines whether or not Excel updates formulas that include references to external data sources (such as Microsoft Word for Windows documents or Microsoft Access databases). Note that Excel workbooks and documents are not external data sources.

When you open a workbook, the Update remote references check box is set according to the conditions in the following table:

                                           Update Remote
   Condition                               References Check Box
   ------------------------------------------------------------

   Workbook not linked to any other        Selected
   documents.

   Workbook linked to other workbooks      Selected
   or Microsoft Excel documents.

   Workbook linked to external data        Cleared (not selected)
   source and you select No when you
   receive the prompt "This document
   contains links. Re-establish links?"

   Workbook linked to external data        Selected
   source and you select Yes when you
   receive the prompt "This document
   contains links. Re-establish links?"

  Workbook linked to external data         Cleared (not selected) 
  source and you select No when you receive 
  the 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?"(Excel 2000)

  Workbook linked to external data         Selected 
  source and you select Yes when you receive 
  the 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?" (Excel 2000)

  Workbook linked to external data         Cleared (not selected) 
  source and you select No when you receive 
  the 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?" (Excel 2002 and Excel 2003)

  Workbook linked to external data         Selected 
  source and you select Yes when you receive 
  the 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?" (Excel 2002 and Excel 2003)

To clear the Update remote references check box in the active workbook, follow these steps:

On the Tools menu, click Options.
Click the Calculation tab.
Click to clear the Update remote references check box.
Click OK to accept the change.

Back to the top

Sample Visual Basic Procedure

The following Microsoft Visual Basic code sample assumes that you have a file called Test.xls, which is located in the C:\Excel directory. The sample code opens the Test.xls file and then clears the Update remote references check box. As the Sub procedure opens the file, it also prevents Excel 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 universal setting: If two workbooks are open, one workbook can have its Update remote references check box selected even though in the other workbook, the check box is cleared. However, when you open a workbook, its Update remote references check box is selected by default, no matter how it was saved, unless it contains links to an external data source.

Back to the top