Great! Thanks for letting me know that it worked for you.

T. Erkson wrote:
Ya-hoooooo! Deb, your suggestion of changing the connection worked PERFECTLY!
It only required two lines of code for my purpose-built add-in. I added one
line to change from ODBC to OLEDB right before my code that makes changes, then
added one line to change back to ODBC from OLEDB -- that's it! All the pivots
updated without refreshing AND the copied pivot tables updated as well (this is
what I wanted) :-) This simple change now also allows some of the other pivot
data sources to be updated that was not possible before because they are part of
a proprietary data pull (best way I can explain it).

This workaround is an unbelievable help and so dumbfoundingly simple; I never
would've thought of it! But you can bet I won't forget it now. Thanks sooooooo
much and feel free to use any of this for your web site.

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






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html