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

View products that this article applies to.

This article was previously published under Q248204

SYMPTOMS

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

This workbook contains queries to external data that refresh automatically.

Queries are used to import external data into Excel, but harmful queries can be used to access confidential information or write information back to a database.

If you trust the source of this workbook, you can enable automatic query refresh. If you disable automatic query refresh, you can later refresh queries manually, if you are satisfied that the queries are safe.

Back to the top

CAUSE

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.

Back to the top

MORE INFORMATION

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.

Back to the top

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.

Back to the top

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:

322756 (http://support.microsoft.com/kb/322756/ ) How to back up and restore the registry in Windows

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:

Exit your version of Excel.
Click Start, click Run, type regedit, and then click OK.
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.
After you select the key that is specified in step 3, point to New on the Edit menu, and then click DWORD value.
Type QuerySecurity, and then press ENTER.
Right-click QuerySecurity, and then click Modify.
In the Value data box, type a valid value for the functionality that you want, and then click OK.
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:

   Value    Effect
   -----------------------------------------------------------------------

   0        Excel prompts you every time that you open a file containing a
            query or PivotTable that has been configured to refresh
            automatically.

            Note: This is the default behavior in Excel 2000 SR-1.

   1        Excel will not prompt you when you open a file containing a
            query or PivotTable that has been configured to refresh
            automatically. Excel will not refresh the query or PivotTable  
            automatically.

   2        Excel will not prompt you when you open a file containing a
            query or PivotTable that has been configured to refresh
            automatically. Excel will refresh the query or PivotTable 
            automatically.

            Note: This is the default behavior in Excel 97 and earlier 
            (non-SR-1) versions of Excel 2000.

Back to the top