can't set CommandText property if cache has >1 rpt

ashortxl · May 8th, 2005, 01:08 AM

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?

BrianWren · May 9th, 2005, 03:37 PM

Reporting error numbers is next to useless; what is the text of the error?

I suspect that you have a circular reference taking place.

ashortxl · May 9th, 2005, 04:46 PM

The VBA error is:

Run-time error '1004'
Application-defined or object-defined error

clicking on debug highlights the line that sets the .CommandText property (which works without error when the cache is not shared)

I do not have a circular reference.

ashortxl · May 20th, 2005, 04:24 PM

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.

Pl0p · February 20th, 2006, 02:06 PM

Quote:

quote:Originally posted by ashortxl
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.

Can you provide a little more detail.
We have a lot of people that have used the ODBC connection on a pivot, and now we are migrating our network drives when people refresh their data Excel comes up with a wizard to change the location of the access database, but that doesn't work :( So i wrote a macro to change the information, but it doesn't work on the .CommandText :( but it does in the .Connection .

Did you re-built the connection from scratch with the wizard or did you code the change with a macro? If coded, can you explain me what I need to change?

paperboy21 · March 29th, 2007, 02:58 PM

Here's how I got this thing to work... it's not the most elegant of solutions, but it works.

Code:

With ActiveWorkbook.PivotCaches(intPCIndex)
    If .QueryType = xlODBCQuery Then
        blnODBCConnect = True
        strSQLConnect = Replace(strSQLConnect, "ODBC;DSN", "OLEDB;DSN", 1, 1, vbTextCompare)
    End If

    If StrComp(.Connection, strSQLConnect, vbTextCompare) <> 0 Then
        .Connection = strSQLConnect
    End If

    If StrComp(.CommandText, strSQL, vbTextCompare) <> 0 Then
        .CommandText = strSQL
    End If

    If blnODBCConnect = True Then
        .Connection = Replace(.Connection, "OLEDB;DSN", "ODBC;DSN", 1, 1, vbTextCompare)
    End If

    .Refresh
End With

Notes:

Basically, I just check to see if the QueryType property of my PivotCache object is ODBC. If so, I replace "ODBC" with "OLEDB" in the Connection property, make my changes to the CommandText property, then replace "OLEDB" back with "ODBC" in the Connection property
intPCIndex is a variable I set based on the Index property of a PivotCache object (also can be gotten as the CacheIndex property of a PivotTable object)
strSQLConnect and strSQL are variables that I am populating with connection string and query text data (respectively) from one of my worksheets

nboisen · September 24th, 2007, 01:14 AM

BRILLIANT!!!
Mr. Paperboy -- Thanks so much for this post.
It has solved a problem I have been struggling with for ages!
You have just saved me hours of work every time I migrate my database to new location, which previously required manually linking 20+ Pivot Tables because of this very problem. I hope you don't mind if I share with others.

fdoorn · April 27th, 2008, 04:51 AM

Excellent post.

I wrote the following functions which I call right before and after I update the CommandText.

Code:

Private Sub dbODBCtoOLEDB()
    Dim pcPivotCache As PivotCache
    Dim strConnection As String
    For Each pcPivotCache In ActiveWorkbook.PivotCaches
        If pcPivotCache.QueryType = xlODBCQuery Then
            strConnection = _
                Replace(pcPivotCache.Connection, "ODBC;DSN", "OLEDB;DSN", 1, 1, vbTextCompare)
            pcPivotCache.Connection = strConnection
        End If
    Next pcPivotCache
End Sub

Private Sub dbOLEDBtoODBC()
    Dim pcPivotCache As PivotCache
    Dim strConnection As String
    For Each pcPivotCache In ActiveWorkbook.PivotCaches
        If pcPivotCache.QueryType = xlOLEDBQuery Then
            strConnection = _
                Replace(pcPivotCache.Connection, "OLEDB;DSN", "ODBC;DSN", 1, 1, vbTextCompare)
            pcPivotCache.Connection = strConnection
        End If
        pcPivotCache.Refresh
    Next pcPivotCache
End Sub

Wouldn't have been able to do it without the original poster :D

arbyter · December 30th, 2009, 12:00 PM

thanks a lot for this hint: a real livesaver

willb4ever · March 11th, 2010, 02:24 AM

Awesome. I had all but given up on a workaround, and stumbled upon this while looking up other properties of the PivotCache class. What can I say. You're a life saver.

This is a known MS bug and is documented at http://support.microsoft.com/kb/816562/. Apparantly, though not documented in this MS article, this bug still exists in 2007 (not sure about 2010 though), and according to MS, they're unaware of this workaround (or any workaround, for that matter).

What can I say. Awesome :-D