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.

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

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.
>
>

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