macro to disable auto refresh

[Patrick Bateman]

hi

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

thankyou

 

[ddiicc]

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]

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

 

[Tom Ogilvy]

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.

 

[Patrick Bateman]

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]

If you are using a periodic refresh

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

 

[Patrick Bateman]

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]

Read the help on EnableRefresh

for each sh in worksheets
for each qt in sh.QueryTables
qt.EnableRefresh = false
Next
Next
Thisworkbook.Save

 

[Jason]

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

 

[Patrick Bateman]

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]

Now that I reopened the sample workbook I created yesterday, I see
what you're talking about. Unfortunately, I don't know how to disable
that. I found an article in the Microsoft help site, but I don't know
that it will help unless you want to change a registry setting.
Here's the URL, just in case:
http://support.microsoft.com/kb/248204/zh-cn

Another option I thought of was using a new workbork that's linked to
the workbook with the queries. To disable the external links startup
dialog, click Edit --> Links and then in the Edit Links dialog, click
the Startup Prompt button in the bottom left.

Hope this helps
Jason

 

[Patrick Bateman]

Thank you Jason, i dont really want to change the registry setting, but am
thinking does it even matter what is chosen in the prompt ? as when i look at
the data range properties after "enable automatic refresh" is selected it is
still set to not enable auto refresh or refresh on file open?

Regards

Patrick

 

[Jason]

Patrick,
I apologize for the delay. I'm not sure what causes the prompt,
because, like you, I see the prompt regardless of the setting in the
data range properties. I wish I could be more help. I'll keep an eye
out for a solution. If I find anything, I'll post it here.