How do I view Pivot Table source data file and field names?

[Guest]

I'd like information on how you can view the name/reference for the source
data of a Pivot Table. For Example, in MS Access and Cognos Impromptu you
can go to SQL view and see where data in a Query is coming from. It's easy
to find the source of formulas in Excel, using the Audit Toolbar. I'd like a
way to quickly view the file name (whether an Excel file or external source)
and field names that were used to create the Pivot Table.

 

[Paul Sheppard]

I'd like information on how you can view the name/reference for the
source
data of a Pivot Table. For Example, in MS Access and Cognos Impromptu
you
can go to SQL view and see where data in a Query is coming from. It's
easy
to find the source of formulas in Excel, using the Audit Toolbar. I'd
like a
way to quickly view the file name (whether an Excel file or external
source)
and field names that were used to create the Pivot Table.

Hi chocolate2346

Try this, right click on the Pivot Table, click Wizard, a dialogue box
will appear, click the Back button, a second dialogue box will appear
that shows the source of the data

 

[Guest]

No, I'm sorry but the suggestion does not give me what I'm looking for. For
example, the Pivot Tables that I'm currently working with are created from
queries in MS Access databases. When I click the back button as suggested,
below, I get the Get Data button. When I click the Get Data button, I get a
list of "Available Tables and Columns". I cannot see what the name of the
..mdb file is that the Tables and Columns/Fields are coming from. That window
also shows a list of "Columns in your query". However, it does not identify
the Table or Query that each column came from.

 

[Debra Dalgleish]

You can use programming to print the commandtext:

Sub PrintCommandText()
Debug.Print ActiveWorkbook.PivotCaches(1).CommandText
End Sub

 

[Guest]

I found it!!

Go to Tools, Macro, Microsoft Script Editor. Do a search (Ctrl + F) for
QuerySource. It takes me to just what I'm looking for. It shows the Name of
the file and the Query/Table and Fields.

Hooray!