Sounds good. One question: What menu paths do I use to set the Querytable's
BackgroundQuery to False?

Cheers

"hoppermr" wrote:

I am trying to run a MS Query query in Excel and refreshing a pivot table
afterwards that feeds off the query using VBA. The query dumps the data into
a separate data sheet in Excel. The pivot table reads the Excel sheet (and
is not linked to MS Query directly).

Problem is that the query takes a while to run. The VBA code doesn't wait
for the query to finish before it continues to the next line and refreshes
the pivot tables. The result is that the pivot tables re-refresh but not
based on the latest data.

What I need is code that halts the execution of the VBA until all queries
have run (ie the Query application has closed). Are there other solutions
out there?

Code so far is below:-

Dim wsSheet As Worksheet
Dim qt As QueryTable
Dim pvt As PivotTable

'To update all query extracts in Workbook
For Each wsSheet In Worksheets
For Each qt In wsSheet.QueryTables
qt.Refresh
Next
Next wsSheet

'To update all pivot tables across all worksheets
For Each wsSheet In Worksheets
For Each pvt in wsSheet
pvt.Refresh
Next pvt
Next wsSheet

End Sub