Setting CommandText property of PivotCache fails if cache has >1 PivotTable

I'm using Excel 2003

Ok, pivot table VBA studs, help me out here:

Imagine you already have one pivot table report based on external data,
say a query of an Access DB or perhaps of another Excel file (assume it
was created via the Wizard and Get Data...).

In VBA, the actual query is stored in the CommandText property of the
PivotCache, which is easily viewable by running the code (assumes only
1 pivotcache for simplicity):

MsgBox ActiveWorkbook.PivotCaches(1).CommandText

You can also set the property in VBA by running the code:

ActiveWorkbook.PivotCaches(1).CommandText = "SELECT * FROM MyTable
MyTable"

(WHERE clause omitted for simplicity) The query example above would be
the syntax for querying an external Excel database with a range called
MyTable, and it works great - I can change the query in VBA, and
setting the CommandText property has the effect of refreshing the
cache.

So far so good. Now say I add another pivot table that uses the first
PivotCache as its source (specifying as such in the Wizard), so that
refreshing 2 reports only fires 1 query.

I can still run the code to get the sql:
MsgBox ActiveWorkbook.PivotCaches(1).CommandText

But I cannot run the code to set the sql:
ActiveWorkbook.PivotCaches(1).CommandText = "SELECT * FROM MyTable
MyTable"

It causes a VBA error 1004. If I then remove the 2nd pivot table so
that the cache only has 1 report, it works fine again - I can set the
sql.

This seems clearly to me a bug in Excel. Any clever workarounds? I
did see some threads about setting the property requiring a string
array to get around a string size limitation, but that it is not the
problem, I think that was only in Excel 2000. At any rate in Excel
2003 I can set huge SQL strings, but I can't set anything if the cache
is shared.

Any clever ideas?


--
ashortxl
------------------------------------------------------------------------
ashortxl's Profile: http://www.msusenet.com/member.php?userid=1043
View this thread: http://www.msusenet.com/t-1870413905

I solved the problem. By default, the pivot table wizard / Query
creates an ODBC connection. If I change the connection string to use
OLE DB instead of ODBC, then everything is fixed.


--
ashortxl
------------------------------------------------------------------------
ashortxl's Profile: http://www.msusenet.com/member.php?userid=1043
View this thread: http://www.msusenet.com/t-1870413905