Auto update feature on multiple pages

[Jemmick]

I have a large spreadsheet that is pulling data from an ODBC source and throwing it into a table. From that table I am creating a pivot chart and a pivot table. The data I want my sales people to see is the pivot charts. Here is the issue. I have all the tables and charts setup to autoupdate on open. What I want them to do is update every 10 minutes or so. Here is the other issue. My data "pull" takes about 30-45 seconds so my charts are refreshing before all the new data is pulled. So basically I have to go back and refresh the charts after doing a refresh all. I have began to build some really large programs in excel with 20 or so different sheets pulling and linking data. I feel getting into VB is going to be a must for me in the future.

 

[ChrisM]

If you get in to your Connection properties you will see an option for Background Refresh, make sure it is not checked. You will also see an option to refresh every xx minutes.

There's also no reason why you have to pull the table and pivot table seperately. You can set your pivot table to pull directly from the ODBC, no middle table required.

 

[Jemmick]

If you get in to your Connection properties you will see an option for Background Refresh, make sure it is not checked. You will also see an option to refresh every xx minutes.

There's also no reason why you have to pull the table and pivot table seperately. You can set your pivot table to pull directly from the ODBC, no middle table required.

Just for knowledge what does the background refresh buttons purpose? Also now that I have unchecked it how do I get the pivot chart to update everytime the pivot data does?

 

[ChrisM]

turning off the background option means that events will happen in sequence only (not all at once). You mentioned your charts are updating before the queries are done running. Turning off background refresh would ensure that no further actions occur until the queries are done (the spreadsheet will essentially be locked to all input/actions). But now that I think about it, if your pivot tables are getting refreshed before the ODBC queries then this won't work for you.

Your best solution is what I said earlier, there is no need for a seperate ODBC query. Just set up your pivot table to use the ODBC as a datasource, then you won't have this problem any more.