Auto Refresh Pivot Tables

We have a developed a workbook for project managers that automatically imports all of the time charged to their projects from an access database to their workbook when they open the spreadsheet. The auto-import is enabled through MS Query and users are asked to enable the auto refresh of the query when they open the workbook.

The workbooks also contain are three pivot tables that are set up to utilize the information that comes in from the query. The pivot table are set to refresh on open.

It appears however, that during the process of opening the workbooks, the pivot tables refresh before the query refreshes, meaning that the pivot tables are using not utilizing the most recent data.

If I open the workbooks, refresh the query data, then save, close and re-open the workbooks, the pivot tables do in fact include the most recent data--the auto refresh of the pivot table is working--(unless of course the access database changed between the time of the first opening and the second).

Is it possible to set the auto refresh on the pivot tables to wait until after the query refreshes the data?

If that is not possible, is there a way to get all of the pivot table to refresh automatically once the query has run?

Will an auto-open macro run before or after the data query?

Two suggestions:

1) If your pivot tables are based on the raw data from your access tables, (i.e. you don't have any formulas in the columns adjacent to your imported data), you might want to re-create the pivot tables from scratch, based directly on the external data. This will eliminate the problem of refresh order and may substantially reduce the size of your excel, file and speed up processing.

2) Failing the above, you could disable automatic refresh completely, and use a macro to refresh both the imports and the pivot tables in the correct order.

Captain Quattro has given voice to the solution that I developed yesterday. I've disabled the auto refresh of the MS Query Data and enabled an auto_open macro that asks the user whether to update the MS Query, then updates the pivots tables.

Thanks for your help.