Solved

Excel VBA Querytable Refresh Error

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now