Re-assigning CommandText to an existing PivotCache

[Mark Wickett]

Hi,

I'm trying to write some VBA code to ask the user the new location of an
Access database that is used as the source for an Excel PivotTable, then
points that PivotTable to the new connection.

Pseudo code is this:

With ActiveWorkbook.PivotCaches(1)
.Connection = strDBConn
.CommandText = strDBCTxt
End With

Where strDBConn is the connection string and strDBCTxt is the SQL for the
query. The first part (.Connection) works fine, but Excel halts with a "1004
error" on the assignment of CommandText.

I have checked the contents of the string - and done two tests: first,
re-write the code to create a new PivotCache which works with strDBConn and
strDBCTxt; then a test of the original method where it assigns the current
contents to a temporary string, then re-assigns it back, but it still falls
over with the same error.

What am I doing wrong?

Thanks,
Mark

 

[Mark Wickett]

Answering my own post again... found the solution, and it's related to a bug
when you don't explicitly declare a PivotTable object as a PivotTable (i.e.
declare as Object, or let Excel VBA declare it for you).

Corrected code that works:
Dim ptSrc As PivotTable
....
Set ptSrc = Worksheets("abc").PivotTables("src")
With ptSrc.PivotCache
.Connection = strDBConn
.CommandText = strDBCTxt
End With
....
Set ptSrc = Nothing


Details of the bug are here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;555165

Mark

 

[Mark Wickett]

Scratch that... fell over second time, so I didn't catch it after all...

Oops.
Mark