reference is not valid message for pivot tables based on Access query

[Doug Glancy]

I've looked around but can't find an answer to this.

I have a workbook - an xlt - that has 6 worksheets that pull data from
different queries in the same Access database.

I have several worksheets with on pivot table each. The pivot tables are
based on dynamic named ranges in the external data worksheets. I use
dynamic ranges because I have some adjacent calculated columns in the
worksheets.

The workbook has only 6 pivot caches - the same as the number of worksheets
that pull data. I copied pivot tables from sheet to sheet so that they'd
use the same cache, where appropriate. Some caches feed only one pivot
table, some feed several pivot tables.

All the pivot tables are set to refresh on open, as are the external data
ranges.

This was all working fine, but today I started getting multiple "Reference
is not valid" messages after clicking "enable automatic refresh" when
opening the xlt. If I click on a pivot table, I get a "not a valid pivot
report" message (or something close to that, I don't remember exactly).

One of the last things I did before this started was to change a calculated
field in the Access query that's included in all the queries that these
sheets pull. I changed a Reporting Quarter field from numeric to text, and
the values from 1,2 ... to "Quarter 1"...

I'm also wondering if it's objecting to multiple pivot tables based on a
single cache that's based on a dynamic range in a sheet that pulls from
Access.

Thanks in advance,

Doug

 

[Dick Kusleika]

On Wed, 18 Feb 2009 17:35:17 -0800, "Doug Glancy"


Hi Doug.

This was all working fine, but today I started getting multiple "Reference
is not valid" messages after clicking "enable automatic refresh" when
opening the xlt. If I click on a pivot table, I get a "not a valid pivot
report" message (or something close to that, I don't remember exactly).

One of the last things I did before this started was to change a calculated
field in the Access query that's included in all the queries that these
sheets pull. I changed a Reporting Quarter field from numeric to text, and
the values from 1,2 ... to "Quarter 1"...

Can you make a new extrernal data table in a new workbook from the Access
query? I know that MSQuery doesn't like some Access functions. I think
it's because they use different Jet versions, but I don't remember.

I'm also wondering if it's objecting to multiple pivot tables based on a
single cache that's based on a dynamic range in a sheet that pulls from
Access.

I don' t think this is the problem. At least it has never been a problem
for me.

 

[Doug Glancy]

Dick,

I figured it out. I've got the external data all set to delete content on
save, and the pivot caches all set to refresh on open. It looks like the
caches are trying to refresh from external data that hasn't refreshed yet.
I think I need to control all this from Access, maybe in the WorkBookOpen
event.

Do you have any suggestions?

thanks,

Doug

 

[Dick Kusleika]

Dick,

I figured it out. I've got the external data all set to delete content on
save, and the pivot caches all set to refresh on open. It looks like the
caches are trying to refresh from external data that hasn't refreshed yet.
I think I need to control all this from Access, maybe in the WorkBookOpen
event.

Do you have any suggestions?

Set the pivot tables not to refresh automatically, and set up a QueryTable
class that refreshes all of the pivot tables once the QT is refreshed.

http://www.dailydoseofexcel.com/archives/2004/06/21/classes-other-events/

A simpler, but less easily extended, method is to just set everything to
manual refresh, and do them one-by-one. Be sure to use BackgroundQuery =
False

MyQt1.Refresh False
MyQt2.Refresh False
MyQt3.Refresh False
MyPivot1.Refresh
MyPivot2.Refresh

and so on, in your Open event.

 

[Doug Glancy]

Dick,

Thanks for the suggestions.

Doug

Set the pivot tables not to refresh automatically, and set up a QueryTable
class that refreshes all of the pivot tables once the QT is refreshed.

http://www.dailydoseofexcel.com/archives/2004/06/21/classes-other-events/

A simpler, but less easily extended, method is to just set everything to
manual refresh, and do them one-by-one. Be sure to use BackgroundQuery =
False

MyQt1.Refresh False
MyQt2.Refresh False
MyQt3.Refresh False
MyPivot1.Refresh
MyPivot2.Refresh

and so on, in your Open event.