Can't set CommandText if pivot table copied
 

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.

Can't set CommandText if pivot table copied
 

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 wrote:
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
Contextures
http://www.contextures.com/tiptech.html

Can't set CommandText if pivot table copied
 

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.

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


Debra Dalgleish wrote:

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 wrote:

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
Contextures
http://www.contextures.com/tiptech.html