Pause macro until Power Queries finished refreshing | VBA & Macros | Excel Forum

Hi Blanka,

You should read this topic, it's a similar discussion.

Indeed, it's in the Power Quwry forum for members.

You have 2 options to refresh a connection: with background refresh set to false or to true.
Go to Data-Connections, select the connection you want, click on Properties, and uncheck the checkbox "Enable background refresh"
This way, the next line will not be executed until the ​refresh is complete.

If you have many connections, use a simple code to disable that option:

You will get such code if you use the macro recorder while performing this action manually for a connection. Unfortunately, there is a bug, the code will continue before the last query is completed, here is Gregory's workaround:

Gregory Regan said
I have the solution! Crude but should work...

When refreshing the queries programmatically, they seem to process in alphanumeric order based on the name of the queries. Therefore, create a permanent hidden dummy WorkSheet with a single row of data and then create a simple query on it and name the query "ZZZZZZZZZZZ_DummyQry" or something similar.

When the code loops through the queries, the "ZZZZZZZZZZZ_DummyQry" query will run last (and finish in a fraction of a second) ensuring that last genuine query is finished processing before moving onto the code to protect all worksheets because of the code line Connection.OLEDBConnection.BackgroundQuery = False.  

Yes, should work.

Keep in mind that you don't have to set the BackgroundQuery to false in this code, it's enough f you do it manually, or just run it once to change that setting.