Refreshing MS Query and Excel Pivot Tables using VBA

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

If the Querytable's BackgroundQuery property is True then other things can
process before the query is done; you can set it to False to force Excel (and
your code) to wait. Or you can set up a loop using the Refreshing property;
this allows you to continue to do other things and to implement a timeout if
desired; example below:

For Each wsSheet In Worksheets
For Each qt In wsSheet.QueryTables
QStart = Now()
qt.Refresh
While qt.Refreshing
If Now() > QStart + TimeValue("00:02:00") Then
qt.CancelRefresh
MsgBox "QueryTable " & qt.Name &" timed out"
End If
DoEvents
WEnd
Next
Next wsSheet


--
- K Dales


"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

Alternativley if you are using query to retrive the data - providing you
don't add or adjust the data you can import the results directly in to the
pivot table. In step one of the pivot table wizard select 'external data
source', 'next' and then create your query as normal.

"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

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