View/Change Pivot Table Query
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.
"ToniS" wrote:
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.
|