Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Pivot Table's query
Hi,
I'm trying to change the sql statement of a Pivot Table through VB6. But, on the following line, pt.PivotCache.sql = StringToArray(ExcelWorkbook.Application.Substitute (pt.PivotCache.sql, OldSQL, NewSQL)) I get the following error: 1004 - Application-defined or object-defined error I've also tried with: pt.PivotCache.CommandText = StringToArray(ExcelWorkbook.Application.Substitute (pt.PivotCache.Command Text, OldSQL, NewSQL)) but I get the same error. Any ideias? *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Pivot Table's query
I have discovered, after spending several hours on this topic myself, that a
CommandText or SQL value whose length is between 242 characters and 257 characters (inclusive) will throw this error every time. Don't ask me why. I haven't a clue. I discovered this by starting with a SQL string that caused this problem for me and adding comment text to the end of it or subtracting superfluous spaces from it until it ceased causing an error. It is important to note that I can not duplicate this problem manually in Excel nor by using Excel VBA to edit the CommandText from the workbook itself. It seems to only occur when controlling a Pivot Table (or possibly other query-related Excel objects) through Office Automation. My suggestion to you would be to modify your SQL string to include a healthy chunk of comments at the end to avoid this issue. From everything that I can tell, converting the string to an array does not solve a thing. Andrew D. Wachs "bocachai" wrote: Hi, I'm trying to change the sql statement of a Pivot Table through VB6. But, on the following line, pt.PivotCache.sql = StringToArray(ExcelWorkbook.Application.Substitute (pt.PivotCache.sql, OldSQL, NewSQL)) I get the following error: 1004 - Application-defined or object-defined error I've also tried with: pt.PivotCache.CommandText = StringToArray(ExcelWorkbook.Application.Substitute (pt.PivotCache.Command Text, OldSQL, NewSQL)) but I get the same error. Any ideias? *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I extend an existing pivot table's range | Excel Discussion (Misc queries) | |||
Identifying Pivot Table's Field | Excel Programming | |||
Imitating Pivot table's context awareness | Excel Programming | |||
Accessing Pivot Table's Data in VBA | Excel Programming | |||
Accessing Pivot Table's Data | Excel Programming |