XL2000: Update Remote References Check Box Is Selected by Default (213836)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: 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?"
				

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

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

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.