Patrick

It looks like you are creating a new PivotCache, but nowhere do you change
the PivotCache that's linked to the PivotTable that you are refreshing. If
I understand correctly, you would want something like this

With ActiveSheet.PivotTables("Pivot Tabelle")
.PivotCache.CommandText = sql-statement 2
.Refresh
End With

It seems that you don't need the Add method to create a new PivotCache, just
change the CommandText property of the existing one.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"patrick" wrote in message
...
hello

i want to change the commandtext in pivotcaches

i've an odbc connection, then i create e pivot cache and a pivot table:
With ActiveWorkbook.PivotCaches.Add(xlExternal)
.Connection = _
"ODBC;DSN=test;UID=test;PWD=test"
.CommandText = (sql-statement)
.CreatePivotTable Range("A3"), "Pivot Table"
End With

then in step 2 i want to change the sql statement (p.e. with new date
selection):

With ActiveWorkbook.PivotCaches.Add(xlExternal)
.CommandText = (sql-statement 2)
End With

ActiveSheet.PivotTables("Pivot Tabelle").PivotCache.Refresh

but the cache wasn't refresh on the pivot table. the pivot table looks
with the old date too.

i also try:
set pvtCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
but i work doesn't too.

can anyone help me, please?