Excel 2010, 2013 PivotCaches(1).CommandText gives me error 1004

[Adrian D]

Hello. I have a spreadsheet with a pivot table. I created the spreadsheet by copying data in it and then inserted a Pivot Table which works fine. I am trying to see the SQL code for the pivot table. I inserted the following code in Modules. You can see the different responses I get after each msgbox. There are other properties that show and several that give me the same error.


I do not know how to get the CommandText which I understand should contain the SQL for the Pivot table. Please help. Thank you


Public Sub CommandButton2_Click()
Dim pc As PivotCache
Set pc = ActiveWorkbook.PivotCaches(1)
MsgBox "PC1 " & pc.Index ' "PC1 1"
MsgBox "PC1 " & pc.Application ' "PC1 Microsoft Excel"
MsgBox "PC1 " & pc.RecordCount ' "PC1 6791"
MsgBox "PC1 " & pc.Version ' "PC1 4"
MsgBox "PC1 " & pc.CommandText ' "Runtime Error 1004"
End Sub

 

[Johnny C]

What's the source of data for the pivot table? You could try changing the connection from ODBC to OLE DB or vice versa
you can do it within the VBA :-

pc.Connection = Replace(pc.Connection, "ODBC;DSN", "OLEDB;DSN", 1, 1, vbTextCompare)

or

pc.Connection = Replace(pc.Connection, "OLEDB;DSN", "ODBC;DSN", 1, 1, vbTextCompare)

and see if that works

 

[Adrian D]

Thank you. There is nothing linked. The data is in the same Sheet as the Pivot Table (Sheet1). I tried your code and even that is giving me the same error: Run-time error 1004; Application-defined or object-defined error

 

[Johnny C]

The CommandText property only works if the pivot table is from an external connection. Set the code to break after you've set pc to the pivot table, then look at the pc object in your locals window, that will tell you what properties you can use in the code.

What you will see is this:
: CommandText : <Application-defined or object-defined error> : Variant

Hence it crashes.

You might have more luck finding what you want with the .pivottable object, select a cell in the pivot table and press your commandbutton then use

Set tbl = ActiveCell.PivotTable

and investigate the properties of the tbl object

 

[Johnny C]

Oops, the editor here strips things between chevrons as it thinks it's HTML
What the Locals Window shows for CommandText is
CommandText : Application-defined or object-defined error : Variant