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

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

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

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

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