• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1332
  • Last Modified:

Excel VBA Querytable Refresh Error

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
donisanp
Asked:
donisanp
  • 2
1 Solution
 
donisanpAuthor Commented:
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
 
Fernando GomezDBACommented:
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
 
donisanpAuthor Commented:
The error message seems to be triggered by an application level error.  Restarting windows resolved the issue.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now