Let PowerQueries don't have time to finish with VBA

[stigcorneer]

Hi

I have a VBA script that is supposed to remove the protection of the sheets, update two PowerQueries and then re-protect the sheets.
When I run my macro, the queries do not finish, which causes me to get errors in the result.
I would like script to pause until the first query is finished, then the next query will run, a new break and then the rest of the macro will continue.
Query2 is based on the result from Query1. So I need the first to finish before I run the next one.
How do I accomplish this?



Code below:


Sheets ("SheetA"). Unprotect
Sheets ("SheetB"). Unprotect


Worksheets ("SheetA"). ListObjects ("Query1"). QueryTable.Refresh BackgroundQuery: = False
Worksheets ("SheetB"). ListObjects ("Query2"). QueryTable.Refresh BackgroundQuery: = False



Sheets ("SheetA"). Protect DrawingObjects: = True, Contents: = True, Scenarios: = True
Sheets ("SheetB"). Protect DrawingObjects: = True, Contents: = True, Scenarios: = True

 

[starl]

There may be a way to do it with some Query property - but I don't use them often, so I don't know.
But a general pause can be done using Application.Wait.

So, after your first query, you can do
Application.Wait (Now + TimeValue("0:00:10"))
Which tells the code to wait 10 seconds before going on to the next line.