Tom and Jason,

thankyou both for your help, both methods work in changing the refreshing of
the data, but when the file is re opened the "enable autorefresh" or "disable
auto refresh" box still appears. Any ideas?

Thank You

Patrick

"Jason" wrote:

Patrick,
It sounds like you could deselect the 'Refresh data on file open'
option on all your queries by selecting one of the cells in the
external data range and using the menu Data -- Import External Data --
Data Range Properties. Then you could manually refresh them with
the menu Data -- Refresh Data. I think one of the standard buttons
on the External Data toolbar is a Refresh All button, which should
allow you to update all the queries at once.

If you'd rather use vba to disable the 'Refresh data on file open'
option, I think this would work:

Dim WkSht, Qt

With ActiveWorkbook
For Each WkSht In .Worksheets
For Each Qt In WkSht.QueryTables
Qt.RefreshOnFileOpen = False
Next
Next
End With

You could make another similar macro that would refresh all of the
queries:
Dim WkSht, Qt

With ActiveWorkbook
For Each WkSht In .Worksheets
For Each Qt In WkSht.QueryTables
Qt.RefreshOnFileOpen = False
Next
Next
End With

Hope this helps
Jason

On Aug 29, 10:48 am, Patrick Bateman
wrote:
Tom i am not using a periodic refresh, but if i did, does it only refresh
when the file is open? ie. if it was set to 0 it would not refresh and
otherwise would always refresh at the set interval even when the file is
closed?

It might help you to know what i am doing to understand what i mean.......
i have a file with several data queries which refresh when the file is
opened and viewed, every month targets are set against the data pulled
through in the query and are left to be reviewed by the managers, so at this
point the data is not needed to update as it needs to be the same as it was
at the time the targets were made, after the reviewed it then needs to go
back to updating again. All this needs to be done "at the push of a button"

Thankyou for all your help

Regards
Patrick

"Tom Ogilvy" wrote:
If you are using a periodic refresh

for each sh in worksheets
for each qt in sh.QueryTables
qt.RefreshPeriod = 0
Next
Next

--
Regards,
Tom Ogilvy

"Patrick Bateman" wrote:

i have tried this a couple of times and it keeps causing excel to crash just
after i turn off the auto refresh?

"Tom Ogilvy" wrote:

Turn on the macro recorder and do it manually to one of your queries.

then turn off the macro recorder and look at the code recorded.

--
Regards,
Tom Ogilvy

"Patrick Bateman" wrote:

sorry i'm looking to disable the automatic refresh on a database query i have
in my sheet

"ddiicc" wrote:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

This is stop screen updating while running macro and will actually speed up
the macro by 80%. If you want screen updating, just change the FALSE to TRUE

"Patrick Bateman" wrote:

hi

is it possible to run a macro to disable/enable the automatic refresh on all
queries in a workbook?

thankyou