Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
View/Change Pivot Table Query
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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
View/Change Pivot Table Query
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. -- Thanks, Shane Devenshire "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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
View/Change Pivot Table Query
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 "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "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. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
View/Change Pivot Table Query
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 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. -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
View/Change Pivot Table Query
"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. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
View Pivot Table Source Data | Excel Discussion (Misc queries) | |||
Make table query will work in datasheet view but will not make tab | Excel Discussion (Misc queries) | |||
Excel 2003 view formula within pivot table | Excel Worksheet Functions | |||
How to view summarized items in a pivot table | New Users to Excel | |||
How do I go from "Chart View" to "Pivot Table Form" in a Pivot ta. | Excel Discussion (Misc queries) |