identify data source

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

splot! - 31 Mar 2008 13:39 GMT

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

Reply to this Message

Héctor Miguel - 01 Apr 2008 05:04 GMT

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.
[quoted text clipped - 4 lines]
> 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.

Reply to this Message

splot! - 01 Apr 2008 11:02 GMT

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

Reply to this Message

Héctor Miguel - 02 Apr 2008 01:44 GMT

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.

Reply to this Message

splot! - 03 Apr 2008 13:25 GMT

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

Regards

Chris

Reply to this Message

Ron Coderre - 03 Apr 2008 14:09 GMT

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)

> Hello - I have pivot tablers in Excel 2000 drawing their data from
> queries in Access 2000.
[quoted text clipped - 14 lines]
>
> Chris

Reply to this Message

splot! - 04 Apr 2008 13:51 GMT

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

On Apr 3, 2:09 pm, "Ron Coderre" <ronREMOVETHIScode...@bigfoot.com>
wrote:

> The free Pivot Play PLUS add-in, located at
> Debra Dalgleish's website, may help:http://www.contextures.com/xlPivotPlayPLUS01.html
[quoted text clipped - 4 lines]
>
> Does that help?

Reply to this Message

Rate this thread: