How do we RELIABLY refresh PQ web query connections in VBA?

[Charley]

I have a PQ query, linked to a parameter Table, which updates an Excel Table in my workbook. And I have VBA code that looks sorta like this...

For each URL in a Table column
Write the new URL to a parameter Table
Refresh the PQ connection
Do stuff with the data in the refreshed Table
Next URL

My problem is that the connection doesn't reliably refresh. I've tried to refresh it using MyConnection.Refresh. I've tried using ActiveWorkbook.RefreshAll. I've tried DoEvents. I've tried waits for as long as ten seconds, even though the query takes only about a second when I refresh it manually. I've tried looping thru multiple refreshes. All with no joy.

Sometimes it refreshes but most of the time it doesn't. But it always works manually.

Some of you guys have been doing this for several years. I've only concentrated on PQ today, as my other posts today indicate. Someone MUST have an answer. How the HECK do you refresh web queries RELIABLY with VBA?

Thanks.

Charley

 

[Charley]

A thought...

All the files are txt files in web folders. If I treated the URL as a path to files in a file folder, would that give me better options? Would it reduce overhead? Would PQ allow it? Would it do me any good?

Thanks.

Charley

 

[Charley]

I figured it out! It just worked on a test that looped through 50 URLs.

I split my one macro into three:

Sub GetStarted()
ActiveWorkbook.RefreshAll
DoGlobals
LoopTop
End Sub

Sub LoopTop
(This increments the counter, refreshes the connection using cn.Refresh, runs...
Application.OnTime TimeValue(Now + 2 / 86400), "LoopBottom"
...which launches the LoopBottom macro after a two-second delay, and then quits.
End Sub

Sub LoopBottom
(Do stuff with my updated data.)

'Loop back for another pass.
If the loop counter < max value then LoopTop
End Sub


I'll try it again tonight when everyone is watching Netflix and the connection is slow.

So far, the only problem with this approach is that using Application.OnTime makes the macro impossible to stop using any technique I can think of. So I've been testing it with just a few loops.


Charley