Solved

Excel VBA Querytable Refresh Error

Posted on 2009-04-06
3
1,268 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 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