Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA Querytable Refresh Error

Posted on 2009-04-06
3
Medium Priority
?
1,318 Views
Last Modified: 2012-05-06
Experts,

Whenever I try to execute the code below I get a dialog box stating "Code execution has been interrupted"...
When I go into the code to debug it highlights the "Next qt" in the FOR LOOP string.

There are 30 worksheets in this workbook; 22 of which are "hidden" (changed the visible property on each sheet).  This code worked fine earlier this morning but for some reason it has stopped.

Private Sub cmdRefresh_Click()
                
Dim wb As Workbook
Dim ws As Worksheet
Dim qt As QueryTable
 
'=== Update MS Access Queries
 
Set wb = ActiveWorkbook
      
For Each ws In wb.Worksheets
    For Each qt In ws.QueryTables
        qt.BackgroundQuery = False
        qt.Refresh BackgroundQuery:=False
    Next qt
Next ws
 
End Sub

Open in new window

0
Comment
Question by:donisanp
  • 2
3 Comments
 

Author Comment

by:donisanp
ID: 24079960
Clicking "Continue" on the error message seems to update the query in each sheet, however I don't want to click through each message whenever I refresh...
0
 
LVL 5

Expert Comment

by:FGomez02
ID: 24080011
If you don't want alerts to be displayed try putting the following setence in your code:

Application.DisplayAlerts = FALSE


And at the end of the code turn the sentence to TRUE.

Hope helps.
0
 

Accepted Solution

by:
donisanp earned 0 total points
ID: 24080089
The error message seems to be triggered by an application level error.  Restarting windows resolved the issue.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question