Refresh Data mulitple queries (Excel 2003 SP2)

Topic

New Reply

A worksheet has data populated by 6 different database queries. I need to put one button on the worksheet that refreshes the data from all 6 queries. However the code below generates an error that says “This operation can not be done because the data is refreshing in the background.” Basically, it is looping faster than the database can refresh the data. Is there a method or event in the QueryTables that tells me when one query is done? What other way can I test to be sure each query is done before issuing the Refresh command for the next one?

Thanks folks.

Private Sub cmdRefresh_Click()
Dim i As Integer
Dim wb As Workbook
Dim ws As Worksheet

ActiveWorkbook.Worksheets(“Active”).Select

MsgBox “Updateing the Active Accounts worksheet. Please wait.”, vbOKOnly, “ACTIVE SHEET UPDATE”

Set wb = ActiveWorkbook
Set ws = wb.Worksheets(“Active_Accts”)

For i = 1 To ws.QueryTables.Count
ws.QueryTables(i).Refresh
Next i

End Sub

Reply | Quote

Replies

Try

ws.QueryTables(i).Refresh(BackgroundQuery:=False)

This specifies that the code should wait until the refreshing of the query table has finished, instead of continuing while the refreshing takes place in the background.

Reply | Quote

I did not test this but , you could try the “Refreshing” propery of the query table:

For i = 1 To ws.QueryTables.Count
with ws.QueryTables(i)
.Refresh
Do While .refreshing
loop
end with
Next i

This should keep it looping until the refreshing is done…

Steve

Reply | Quote

If there are no other queries in the workbook and no pivot tables you do not want refreshed, this works as well:

ActiveWorkbook.RefreshAll

Reply | Quote