MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot tables based on external data

Posted by Erwin on August 30, 2001 8:07 AM

I have a workbook with multiple pivot tables. When I select the refresh all option, it connects to the db, obtains the data, and closes the connection. It repeats this for each instance. Is there a way keep the connection open for all instances? It takes about 15 seconds to establish the connection because the db is over the network. Thoughts?

Posted by Mark W. on August 30, 2001 8:29 AM

Do these PivotTables share the same data set?

Posted by lenze on August 30, 2001 10:23 AM

Just a guess, but when you first set up the pivot table via MS Query, check the option and make sure option Keep Connection Open until MS Query is Closed is selected. Don't know if this is the problem or not.

Posted by Erwin on August 30, 2001 11:11 AM

No they don't. I'm wondering if there is something I can do globally that will keep this connection open for all pivot tables