G
Guest
hi
is it possible to run a macro to disable/enable the automatic refresh on all
queries in a workbook?
thankyou
is it possible to run a macro to disable/enable the automatic refresh on all
queries in a workbook?
thankyou
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
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
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
G
Guest
sorry i'm looking to disable the automatic refresh on a database query i have
in my sheet
in my sheet
G
Guest
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.
then turn off the macro recorder and look at the code recorded.
G
Guest
i have tried this a couple of times and it keeps causing excel to crash just
after i turn off the auto refresh?
after i turn off the auto refresh?
G
Guest
If you are using a periodic refresh
for each sh in worksheets
for each qt in sh.QueryTables
qt.RefreshPeriod = 0
Next
Next
for each sh in worksheets
for each qt in sh.QueryTables
qt.RefreshPeriod = 0
Next
Next
G
Guest
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
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
G
Guest
Read the help on EnableRefresh
for each sh in worksheets
for each qt in sh.QueryTables
qt.EnableRefresh = false
Next
Next
Thisworkbook.Save
for each sh in worksheets
for each qt in sh.QueryTables
qt.EnableRefresh = false
Next
Next
Thisworkbook.Save
J
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 --
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
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 --
the menu Data --> Refresh Data. I think one of the standard buttonsData Range Properties. Then you could manually refresh them with
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
G
Guest
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
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
J
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
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
G
Guest
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
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
J
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.
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.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.