You are prompted to enable automatic refresh when you open a workbook in Excel

When you open a workbook in Microsoft Excel, you receive the following message:

This issue occurs when the following conditions are true:

• Your workbook contains at least one PivotTable, database query, or Web query.
• The Refresh data on file open option in the External Data Range Properties dialog box is checked for at least one of the PivotTables, database queries, or Web queries in your workbook.

Microsoft Excel 2000 Service Release 1 (SR-1) prompts you if your workbook contains a PivotTable, database query, or Web query that has been configured to refresh automatically when you open the workbook.

The "Options that are available in the Query Refresh dialog box" section describes each option in the Query Refresh dialog box.

The "How to use the QuerySecurity Registry Entry to Configure Excel" section describes how you can use the QuerySecurity registry entry to configure how Excel handles workbooks that contain queries or PivotTables that are configured to refresh automatically.

Options that are available in the Query Refresh dialog box

If you click Enable automatic refresh, then the query or PivotTable is refreshed when you open your workbook. If you click Disable automatic refresh, then the query or PivotTable is not refreshed when you open your workbook. However, clicking Disable automatic refresh does not prevent you from refreshing the query or PivotTable after you have opened your workbook. The Enable automatic refresh and Disable automatic refresh options affect only the current workbook.

If you click to select the Enable automatic query refresh for all workbooks and do not show this warning again check box, you are not prompted when you open any workbooks that refresh a query or PivotTable when they are opened.

Note The Enable automatic query refresh for all workbooks and do not show this warning again check box is available only in Microsoft Excel 2000 and in Microsoft Excel 2002. In all versions of Excel that are later than Excel 2002, you must manually insert and configure the registry key as documented in the "How to use the QuerySecurity registry entry to configure Excel" section.

How to use the QuerySecurity registry entry to configure Excel

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
You can use the QueryRefresh registry entry to configure how you want Excel to handle opening workbooks that contain a query or PivotTable that refresh automatically. By default, the QuerySecurity entry is not present in the Windows registry. To add the QuerySecurity entry to the Windows registry, follow these steps:

1. Exit your version of Excel.
2. Click Start, click Run, type regedit, and then click OK.
3. Locate and then click to select one of the following registry keys, depending on the version of the product that you are using:
   • For Excel 2003, locate and then click to select HKCU\Software\Microsoft\Office\11.0\Excel\Options.
   • For Excel 2002, locate and then click to select HKCU\Software\Microsoft\Office\10.0\Excel\Options.
   • For Excel 2000 SR-1 and later versions of Excel 2000, locate and then click to select HKCU\Software\Microsoft\Office\9.0\Excel\Options.
4. After you select the key that is specified in step 3, point to New on the Edit menu, and then click DWORD value.
5. Type QuerySecurity, and then press ENTER.
6. Right-click QuerySecurity, and then click Modify.
7. In the Value data box, type a valid value for the functionality that you want, and then click OK.
8. On the File menu, click Exit to quit Registry Editor.

See the "Valid settings for the QuerySecurity entry" section for a description of the valid values that are mentioned in step 7 of the previous procedure. This change takes effect the next time that you start Excel.

Valid settings for the QuerySecurity entry

The following table contains the valid settings for the QuerySecurity entry: