Change Pivot Table's query

[bocachai]

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 ***

[Andrew_Wachs]

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 ***
>