View/Change Pivot Table Query

[ToniS]

I am currently using Excel 2003. I would like to look at a PivotTable's SQL
Query, but unsure on how to do this. I did find one posting with a VBA
solution, so I tried it. I get the following error:
'Unable to get the Pivot Tables property of the worksheet class'

Below is the source code

Sub Get_PT_Source_Code()
Dim pvtTable As PivotTable
Dim index As Integer

Set pvtTable = ActiveSheet.PivotTables("PivotTable")

With pvtTable.PivotCache

Debug.Print .CommandText 'the SQL Query
Debug.Print .Connection 'the connection
End With

End Sub

I basically would like to view and maybe change how the pivot table is
displaying the data. Any help would be greatly appreciated.

Thanks
TSharp.

 

[ShaneDevenshire]

Hi,

I don't have time to test now but 1. Select your pivot table and choose
PivotTable, Table Options, and note the Name of the pivot table.
2. Replace "PivotTable" with the correct name.

 

[ToniS]

THANK YOU, I thought the name of the worksheet was the name of the table,
The worksheet name is PivotTable and the actual name of the Pivot table is
Pivot Table4.. Thanks again for your help

 

[Debra Dalgleish]

You can also use Ron Coderre's Pivot Play Plus add-in to view or change
the connection string:

http://www.contextures.com/xlPivotPlayPLUS01.html

 

[sherry]

I am currently using Excel 2003. I would like to look at a PivotTable's SQL
Query, but unsure on how to do this. I did find one posting with a VBA
solution, so I tried it. I get the following error:
'Unable to get the Pivot Tables property of the worksheet class'

Below is the source code

Sub Get_PT_Source_Code()
Dim pvtTable As PivotTable
Dim index As Integer

Set pvtTable = ActiveSheet.PivotTables("PivotTable")

With pvtTable.PivotCache

Debug.Print .CommandText 'the SQL Query
Debug.Print .Connection 'the connection
End With

End Sub

I basically would like to view and maybe change how the pivot table is
displaying the data. Any help would be greatly appreciated.

Thanks
TSharp.

 

[sherry]

An easy way to reference the pivottables is by their index , eg
xlapp.ActiveSheet.PivotTables(1).PivotFields("NAME")

In my application I tend to have only one pivottable per worksheet, but if I
had more I would know about it.

You could use this to give the pivot table a more meaningful name -
xlapp.ActiveSheet.PivotTables(1).name = "whatever", and then reference it by
name throughout the rest of the code.