Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Stupid CommandText Problem | Excel Programming | |||
CommandText | Excel Programming | |||
pivot table Commandtext causes runtime error 1004 in excel 2000 | Excel Programming | |||
CommandText Property | Excel Programming | |||
change commandtext in pivotcaches | Excel Programming |