Dealing with macros that include refreshing web query?

[JumboCactuar]

Hi,
I have a workbook that contains many refreshable web queries which I refresh using a command button.

But how can I "pause" the VBA until queries have finished? As they take up to 2 minutes to complete and I want to manipulate the data afterwards

Appreciate any help

 

[Coding4Fun]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

Add this to your refresh and it will stop if from moving on until the refresh completes.

 .Refresh BackgroundQuery:= False

</code>

 

[JumboCactuar]

I tried this but I get
Compile error:
Wrong number of arguments or invalid property assignment

The queries were created with powerquery

My current code to refresh is

 ActiveWorkbook.Connections("Query - Table 3"). refresh

 

[JumboCactuar]

Bump

 

[Coding4Fun]

Try this, assuming the top left cell of your query table is in Sheet1 Cell A1 (Change Accordingly)

Sheets("Sheet1").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

 

[Coding4Fun]

You might also check the properties of the connection and queries to make sure it is set to Enable Background Refresh, it is an option within the settings of the connection and i believe I saw it in the query as well.

 

[JumboCactuar]

Thankyou I will try it

I just thought when it says refreshing in status bar, there would be a way to check this status in VBA and prevent any other macros whilst it's running

 

[g_sinclair]

To be honest, I'm surprised that the sheet still runs for you while the web queries are processing. Normally when ever my VBA runs doing web queries or other intensive things the entire sheet hangs and there's no way to do anything else (thus no need to prevent other macros from running)

So much so that I usually put a bunch of

DoEvents

calls throughout the loops/processing so the sheet remains vaguely responsive!

 

[Coding4Fun]

DoEvents

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/doevents-function

Not always a good thing but it might help in your case. For example if your using ActiveWorkbook and you start clicking around in other screens or other workbooks the ActiveWorkbook might not be right at the time the code is executed.

Also if you have a query set to BackgroundQuery=True then it is possible for your code to continue and finish before the query returns its results.

BackgroundQuery=False says "Do not continue until I am done querying"

 

[JumboCactuar]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

Add this to your refresh and it will stop if from moving on until the refresh completes.

 .Refresh BackgroundQuery:= False

</code>

Thankyou, I got this working but not with VBA

Queries + Connections tab > then manually going into the properties of each
Unticked Background Refresh
Ticked - Fast Load (unsure how this works)