I've created my own add-in that is akin to Ron's but it's focused on a single
data source and can affect query & pivot tables equally. It's this add-in that
I'm working with for this particular [pivot table] issue.
I looked at the MS article. "Application.Substitute..." doesn't compile nor even
appear in my Excel Help files. ??? But I saw at the bottom of the article this
that was an only indication that it cannot be done:
"If multiple PivotTables on a worksheet are derived from the same PivotTable,
the subroutine does not work after it processes the first PivotTable. As of
March 2003, there is no known workaround for this problem."
I can surmise that this applies to my situation as well :-( So that question is
answered, it is a problem w/Excel that all of us have to suffer with. It sure
would be nice it pivots were given the same easibility as query tables!
I'll play with the code you supplied below, adapting it to my situation and
we'll see if that is a workaround or not. Thank you for the assistance :-)
Toby
"Debra Dalgleish" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 1) I don't know of a way to prevent a refresh when you change the command
> text. Could you write the values to a worksheet, and use those during a
> refresh?
>
> 2) The problem with shared pivot caches is noted at the bottom of this
> article.
>
> http://support.microsoft.com/kb/816562/
>
> However, you can try temporarily changing the connection 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
> '=================
>
> Also, 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.
>
>
>
> Toby Erkson wrote:
>> Version: Excel 2003 SP2
>> It's been awhile since I had to work with pivot tables in VBA so I went to
>> Debra D.'s excellent web site (http://www.contextures.com/) to get some pivot
>> info (and I saw more new stuff!) but couldn't find an answer :-(
>>
>> Two questions, while in VBA:
>> 1) Can one change a pivot table query (the SQL code and connection string)
>> without having it perform an automatic refresh? If so, specifically how?
>>
>> 2) I can make changes to a pivot table (the SQL code and connection string)
>> in VBA without an issue (it gets data from an external source). Here's the
>> interesting part: If I copy the pivot table I am no longer able to make
>> changes to either the original nor the copy. However if I delete one of the
>> pivot tables I am then able to make changes to it. Why is this and is there
>> a way around it? BOTH pivot tables keep the same .SourceType of xlExternal.
>>
>> TIA!,
>> Toby
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
Similar Threads
