Changing SQL Statement for PivotCache linked to multiple tables

[John Michl]

I've stumbled along with some VBA programming that allows me to change the
connection and sql properties of a pivot cache. Unfortunately, I've learned
that it is difficult (or perhaps impossible) to change the SQL statement for
a pivotcache that is linked to more than one pivot table. In my workbook, I
have a dozen or so pivottables most of which were created by either copying
the first table or creating a new table that used the first table as the
data source.

1) Does anyone know of a work around that would allow me to change the SQL
properties of these linked tables? (I've got the code working fine for a
single table but not tables based on one central source.)

2) Is there away to determine which table is the initial table from which
the others are derived? When I look at the connection properties they all
seem to look like they connect to the SQL database not to a pivot table.

Thanks.



P.S. I've left a number of messages regarding similar topics over the past
week with no response. Sorry for being a pest but I'm in a world of hurt.

- John

 

[John Michl]

Thanks, Tom.

Are you aware of a way to change the SQL statement for a pivot cache that
used by more than one table?

- John

 

[John Michl]

Tom, thanks for the response but I can't seem to change the database and SQL
string when the pivottable cache is "shared" among several tables. The code
below will NOT work when I use on a table that has a shared pivotcache.
However, it WILL work on a table that does not share a cache. Any ideas?

Sub ChangeDatabase()
Dim ptc As PivotCache, OldDB As String, NewDB As String
Set ptc = ActiveCell.PivotTable.PivotCache

'Request the name of the old and new database name.
OldDB = InputBox("Input the name of the old database as listed in
the Pivot Tables SQL string.")
NewDB = InputBox("Input the name of the new database.")

ptc.Connection = Application.Substitute(ptc.Connection, OldDB,
NewDB)

' ERROR AT NEXT LINE Run-time Error '1004'
Application-defined or object-defined error

ptc.Sql = Application.Substitute(ptc.Sql, OldDB, NewDB)
ptc.Refresh

End Sub