Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Vass
 
Posts: n/a
Default enable automatic refresh

I have a data import (sql queried)
thats set on an automatic refresh

But when the worksheet is opened, I get an alert dialog box "enable
automatic refresh" or "disable auto..."

how do I switch off this alert and leave 'Automatic Refresh' turned on?

thanks

--
Vass


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary Brown
 
Posts: n/a
Default enable automatic refresh


See Microsoft Knowledge Base article below

You are prompted to enable automatic refresh when you open a workbook
in Excel
View products that this article applies to.
Article ID : 248204
Last Review : July 1, 2005
Revision : 3.1
This article was previously published under Q248204
Important This article contains information about how to modify the
registry. Make sure to back up the registry before you modify it. Make
sure that you know how to restore the registry if a problem occurs. For
more information about how to back up, restore, and modify the registry,
click the following article number to view the article in the Microsoft
Knowledge Base:
256986 (http://support.microsoft.com/kb/256986/) Description of the
Microsoft Windows registry
On This Page
SYMPTOMS
CAUSE
MORE INFORMATION
Options available in the Query Refresh dialog box
Using the QuerySecurity registry entry to configure Excel
Valid settings for the QuerySecurity entry
APPLIES TO

SYMPTOMS
When you open a workbook in 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 message 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 Available in the Query Refresh Dialog Box" section of this
article describes each option in the Query Refresh dialog box.

The "Using the QuerySecurity Registry Entry to Configure Excel" section
of this article describes how you can use the QuerySecurity registry
entry to configure how Excel handles workbooks that contain queries or
PivotTables configured to refresh automatically.
Back to the top

Options 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 the Enable automatic query refresh for all workbooks and
do not show this warning again check box, then you are not prompted
when you open any workbooks that refresh a query or PivotTable when
opened.

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

Using the QuerySecurity registry entry to configure Excel
Warning Serious problems might occur if you modify the registry
incorrectly by using Registry Editor or by using another method. These
problems might require that you reinstall your operating system.
Microsoft cannot guarantee that these problems can be solved. Modify
the registry at your own risk.
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: 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.


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=520993

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
David McRitchie
 
Posts: n/a
Default enable automatic refresh

or you could just refer to http://support.microsoft.com/kb/248204
rather than copying it...


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
2003 Automatic Query Refresh Dean Setting up and Configuration of Excel 0 March 10th 06 12:32 PM
Enable automatic refresh link Deb Bagby Excel Worksheet Functions 0 November 23rd 05 10:02 PM
Enable automatic refresh Deb Bagby Excel Worksheet Functions 0 November 23rd 05 09:31 PM
Enable Automatic Refresh Terri Excel Worksheet Functions 1 August 8th 05 11:26 PM
Query Refresh-Enable Automatic Refresh Dialogue Box Terri Excel Discussion (Misc queries) 0 May 6th 05 08:21 PM


All times are GMT +1. The time now is 05:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"