Solved

Excel VBA Querytable Refresh Error

Posted on 2009-04-06
3
1,293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

627 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