identify data source

[splot!]

Hello - I have pivot tablers in Excel 2000 drawing their data from
queries in Access 2000.

When I need to go back to the source data I need to know the name of
the query the pivot table is using. The only way I have found of
doing this is to go into the wizard for the pivot table, go back to
'get data' then press 'cancel' whereupon I am offered the cahnce to
edit the query in MSQuery. If I do this I can then see the name of
the Access table or query the pivot refers to.

This seems like a lot of ket strokes to find a simple piece of
information and, if there is a lot of data in the source, it can take
a while to load up in MSQuery. I am convinced there must be a better
way but I have yet to find it. Can anyone help please?

Regards

Chris

 

[Héctor Miguel]

hi, Chris !

I have pivot tablers in Excel 2000 drawing their data from queries in Access 2000.
When I need to go back to the source data I need to know the name of the query the pivot table is using.
The only way I have found of doing this is to go into the wizard for the pivot table
go back to 'get data' then press 'cancel' whereupon I am offered the cahnce to edit the query in MSQuery.
If I do this I can then see the name of the Access table or query the pivot refers to.
This seems like a lot of ket strokes to find a simple piece of information and
if there is a lot of data in the source, it can take a while to load up in MSQuery.
I am convinced there must be a better way but I have yet to find it. Can anyone help please?

for querytables and/or pivottables in the activesheet, you could try from immediate code pane (vba-editor)

? activesheet.pivottables(1).pivotcache.connection

? activesheet.querytables(1).connection

? activesheet.querytables(1).sql

hth,
hector.

 

[splot!]

Thanks Hector, that's certainly something I wouldn't have known to
look at.
Unfortunately it doesn't quite go far enough, AFAICS. It shows the
database being used and its location but not the specific query or
table within the access database.
I fear this is going to become more complicated and not offer any real
savings over my current convoluted method.

I guess the only thing to do is to make a note beside each pivot table
telling me the table or query it is drawing on, something I have
started to do anyway. Just seems a bit untidy, that's all.

Regards

Chris

 

[Héctor Miguel]

hi, Cristopher !

Thanks Hector, that's certainly something I wouldn't have known to look at.
Unfortunately it doesn't quite go far enough, AFAICS.
It shows the database being used and its location but not the specific query or table within the access database...

I just made a connection to an access .mdb to build a PT and here is how I got the query
(same as previous post, using immediate window pane from vba-editor)

? activesheet.pivottables(1).pivotcache.sql

hth,
hector.

 

[splot!]

Ah, yes indeed that certainly does help. Thanks very much Hector.

Regards

Chris

 

[Ron Coderre]

The free Pivot Play PLUS add-in, located at
Debra Dalgleish's website, may help:
http://www.contextures.com/xlPivotPlayPLUS01.html

It allows you to view/edit the Data Source and SQL code
for Pivot Tables and Query Tables that use external data sources.
(It has a handful of other features, too)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[splot!]

Thanks Ron,

It certainly looks as though it ought to help.
Unfortunately when I clock on the new option in the Data menu I get:
Run-time error '438'. Object doesn't support this property or method.
:-(

Regards

Chris