Changing SQL Statement for PivotCache linked to multiple tables
 

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

Changing SQL Statement for PivotCache linked to multiple tables
 

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

"Tom Ogilvy" wrote in message
...
PivotTables do not use other pivottables as the data source under the
scenario you describe. What they do is use the original pivotcache as the
same datasource.

Dim pCache as PivotCache
set pCache = ActiveSheet.PivotTables(1).PivotCache

will give you a reference to the pivot cache.

Regards,
Tom Ogilvy


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

Changing SQL Statement for PivotCache linked to multiple tables
 

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

"Tom Ogilvy" wrote in message
...
Same as you would for a pivotcache used by a single table.

The pivot Table settings determine what is shown, but what is available to
be shown is the same for all the tables tied to that single cache. Think
of
the cache as an intermediate data table with some extra meta data.

Regards,
Tom Ogilvy

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

"Tom Ogilvy" wrote in message
...
PivotTables do not use other pivottables as the data source under the
scenario you describe. What they do is use the original pivotcache as
the
same datasource.

Dim pCache as PivotCache
set pCache = ActiveSheet.PivotTables(1).PivotCache

will give you a reference to the pivot cache.

Regards,
Tom Ogilvy


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