Link to home
Start Free TrialLog in
Avatar of donisanp
donisanp

asked on

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

Avatar of donisanp
donisanp

ASKER

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...
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.
ASKER CERTIFIED SOLUTION
Avatar of donisanp
donisanp

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial