How to find the data source

[MVP - WannaB]

Hi, and Thank you for any help.
I have inherited a hand full of Pivot table spreadsheets that Pull data in
from an Access DB, and the query used in the spread sheet needs to be
modified (it is using some Dates that should not be hard coded). There are a
few that I just can not find any Links, or Data Queries, nothing that looks
like it is calling out to the data source, except it does prompt me to
refresh the data when I open the file. I've found all the hidden sheets, and
named ranges (I think) I've looked through all the VBA also.
How do I find what I can't see??
Thanks...

 

[Dick Kusleika]

Hi, and Thank you for any help.
I have inherited a hand full of Pivot table spreadsheets that Pull data in
from an Access DB, and the query used in the spread sheet needs to be
modified (it is using some Dates that should not be hard coded). There are a
few that I just can not find any Links, or Data Queries, nothing that looks
like it is calling out to the data source, except it does prompt me to
refresh the data when I open the file. I've found all the hidden sheets, and
named ranges (I think) I've looked through all the VBA also.
How do I find what I can't see??
Thanks...

It sounds like the pivot tables are pulling directly from the database
rather than a querytable on a worksheet. Go to one of the offending pivot
tables, select a cell somewhere in it, then issue this in the Immediate
Window

?activecell.PivotTable.PivotCache.Connection

If the PT is based on an external data connection, this will tell you which
file it's pulling from. If that works, then do this

?activecell.PivotTable.PivotCache.CommandText

That will give you the SQL string.

To access the Immediate Windows, open the VBE and press Cntl+G.

 

[Shane Devenshire]

Hi,

It's most likely handled from an SQL statement if VBA. Press Alt+F11 and
check each of the possible locations for code - the sheet1 objects, modules
or thisWorkbook.

Next you can look at MS Query, I don't think it can handle a parameter query
but I could be wrong.

1. Click in the pivot table and choose PivotTable, Pivot Table Wizard, Back,
Get Data,
2. Click Next once and see if any conditions are showing for Filter Data,
probably not,
3. Click Next twice and choose View data or edit query in Microsoft Query,
Finish
4. Click the 7th button from the left - Show/Hide Criteria.
5. This area might help you if you are running the query via code

Last, and least likely, there is a parameter in Access, I would expect that
to create real problems but you can always open the Access Database and if
the data is coming from a query you can check it out.

 

[MVP - WannaB]

I am moderately well versed in Access and VBA, but this Excel linking to
databases, WOW, Whole new area.

Thank you to Both Dick Kusleika, and Shane DevenShire!!! Both suggestions
were very helpful, and useful.

The immediate window in VBA editor is not something I use, but I remember it
now that I see it again, and that display's the link to the Access file,
which I was able to change to a UNC path rather then a mapped drive. Also
the query was referencing a field name that was changed.

Thank you both again, these tools you've provided will be very helpful in
days to come..
==================================