RefreshAll web query problem

[andymqld]

Dear All,

I have a daily worksheet which contains numerous web tables.

Each day when I create one of these worksheets the number of webtables included vary. The number of tables is a variable, not a constant.

During the course of a day I refresh the data/tables. I then have some code that utilises that data for some sorting and calculation purposes.

As the number of tables vary I use the code "ActiveWorkbook.RefreshAll".

This should work fine, but unfortunately the rest of the code runs and finishes before the web table refreshing is completed, therefore I don't end up receiving the updated data.

I've tried "QueryTable.Refresh BackgroundQuery:=False" and also "Application.EnableEvents = False", but I still can't get it to pause the rest of the code until the refreshing has finished.

Any ideas?

Regards,

andymqld

[NateO]

Hello,

The RefreshAll Method is really overrated, avoid it. It's really good for creating denial-of-service attacks on DBs (I've seen it), but other than that...

The background argument should be set to False by default on a Web Query as mentioned here:

http://www.xtremevbtalk.com/showthread.php?t=227989

Try looping through your tables, e.g.,

[vba]
Sub foo()
Dim wbQry As QueryTable
For Each wbQry In Worksheets(1).QueryTables
wbQry.Refresh False
Next
MsgBox "Voila!"
End Sub[/vba]
This worked for me, two tables refresh then the msgbox, i.e., refreshing the query takes procedural focus. The trick is that you have to specify the Worksheet that houses the QueryTable Objects, as I have done.

Good hunting.