ExcelBanter - Setting CommandText property of PivotCache fails if cache has 1 PivotTable

I'm using Excel 2003

Ok, pivot table VBA studs, help me out he

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 i
was created via the Wizard and Get Data...).

In VBA, the actual query is stored in the CommandText property of th
PivotCache, which is easily viewable by running the code (assumes onl
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 MyTabl
MyTable"

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

So far so good. Now say I add another pivot table that uses the firs
PivotCache as its source (specifying as such in the Wizard), so tha
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 MyTabl
MyTable"

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

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

Any clever ideas

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