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" wrote in message
...
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