change commandtext in pivotcaches
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? |
change commandtext in pivotcaches
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? |
change commandtext in pivotcaches
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. |
change commandtext in pivotcaches
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. |
All times are GMT +1. The time now is 10:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com