Refreshing External Data on File Open

[grubstar]

Hi,

I want to set my computer to prompt yes/no whether to get the external data
via the queries I have inserted before opening the xls file. It did this
previously but I clicked the checkbox not to prompt me anymore.

Where is this setting? Do I have to edit the registry?

Thanks

 

[Tom Ogilvy]

I don't know specifically, but here are a couple of places to look:

select the data and go to the external data toolbar then:

(from help)

Refresh automatically: You can refresh an external data range
automatically when you open the workbook, and optionally save the workbook
without saving the external data, so that the workbook file size is reduced.
On the External Data toolbar, click Data Range Properties and then select
the Refresh data on file open check box. To save the workbook with the query
definition but without the external data, select the Remove external data
from worksheet before saving check box.

I would assume you would unselect the Refresh data on file open checkbox and
then perhaps you would get the prompt. Another place to look is in
tools=>Options=>Calculate tab. the is a checkbox for Update Remote
References - that may have an effect as well.

Post back and let us know if either of these work.

 

[grubstar]

Hi,

Refresh control checkboxes and tools=>Options=>Calculate tab both don't
work.

The function is a property of Excel rather than a specific file as after I
switched it off none of my spreadsheets prompted me, so I thought it must be
a registry setting as I couldn't find any Tools/Options to work. Can't find
anything in the registry though...

Problem is I can't get the message up again so I can google it, to find an
answer!

Thanks for you help anyway.

 

[Dick Kusleika]

grubstar

Bill Manville told me about this KB article

http://support.microsoft.com/?id=248204

I had to install SR-1a for Office 2000 in order for it to work, but it
worked like a charm after that.