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?

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?

okay, thanks, but then i have a runtime error 1004 on the line where i
want to set the sql-statement 2...

perhaps it's the sql-statement. if i use an easy sql-statement, then it
works.
but i use the same sql-statement like the first one. it has just another
strings from the inputform.

it looks like this:

"SELECT ... FROM ... WHERE Date='" & sDate1 & "' AND Date<='" & sDate2
& "'"

any ideas?

Dick Kusleika wrote:
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.

Patrick

It may be that sDate1 and sDate2 are in the wrong format. I presume they
are strings and not actual dates. If they are actual dates, then you should
use the Format function to get them in the correct format. Next, look at an
SQL statement that works with dates in the WHERE clause. Before you change
the PivotCache.CommandText, add a line like this

Debug.Print .PivotCache.CommandText

and look closely at the way it reads particularly around the dates. I've
seen different programs use slightly different formats for dates, such as

'9/2/2003'
#9/2/2003#
'2003-09-02'

Just make sure you sDate variables and the string that you are creating for
the SQL match up to what you know works.

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

"patrick" wrote in message
...
okay, thanks, but then i have a runtime error 1004 on the line where i
want to set the sql-statement 2...

perhaps it's the sql-statement. if i use an easy sql-statement, then it
works.
but i use the same sql-statement like the first one. it has just another
strings from the inputform.

it looks like this:

"SELECT ... FROM ... WHERE Date='" & sDate1 & "' AND Date<='" & sDate2
& "'"

any ideas?

Dick Kusleika wrote:
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.