Can't set CommandText if pivot table copied

[steveh]

I have a pivot table on sheet1 that I can change the query parameters
using the PivotCache.CommandText property. But if I copy that pivot
table onto a new worksheet, or copy the entire worksheet, I can no
longer set the PivotCache.CommandText for either pivot tables. If I
delete the copied worksheet, I can set the CommandText again on the
original pivot table.

Does anyone have any solutions?

Thanks for looking.

 

[Debra Dalgleish]

If it's an ODBC connection, you can temporarily change it to OLEDB, then
change the command text. For example:

'==============
Sub SetCmdText()
Dim pc As PivotCache
Dim strConn As String
Dim strConn2 As String
Set pc = ActiveWorkbook.PivotCaches(1)
strConn = pc.Connection
strConn2 = Replace(strConn, "ODBC", "OLEDB")

pc.Connection = strConn2
pc.CommandText _
= ActiveSheet.Range("rngCmdTxt2").Value
pc.Connection = strConn
ActiveWorkbook.RefreshAll

End Sub
'=================

 

[steveh]

I must be doing something wrong because that didn't work for me.

 

[Debra Dalgleish]

Ron Coderre has a pivot table add-in that lets you view and edit a pivot
table's connection string and command text, in Excel 2002 or Excel 2003:

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

Maybe that will help.