Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 8th, 2005, 01:08 AM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default can't set CommandText property if cache has >1 rpt

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?


  #2 (permalink)  
Old May 9th, 2005, 03:37 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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

I suspect that you have a circular reference taking place.
  #3 (permalink)  
Old May 9th, 2005, 04:46 PM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


  #4 (permalink)  
Old May 20th, 2005, 04:24 PM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


  #5 (permalink)  
Old February 20th, 2006, 02:06 PM
Registered User
 
Join Date: Feb 2006
Location: , , Belgium.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
  #6 (permalink)  
Old March 29th, 2007, 02:58 PM
Registered User
 
Join Date: Mar 2007
Location: Bolivar, OH, USA.
Posts: 1
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
The Following User Says Thank You to paperboy21 For This Useful Post:
  #7 (permalink)  
Old September 24th, 2007, 01:14 AM
Registered User
 
Join Date: Sep 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
The Following User Says Thank You to nboisen For This Useful Post:
  #8 (permalink)  
Old April 27th, 2008, 04:51 AM
Registered User
 
Join Date: May 2006
Location: Amsterdam, NH, Netherlands.
Posts: 1
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
The Following User Says Thank You to fdoorn For This Useful Post:
  #9 (permalink)  
Old December 30th, 2009, 12:00 PM
Registered User
 
Join Date: Dec 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks a lot for this hint: a real livesaver
  #10 (permalink)  
Old March 11th, 2010, 02:24 AM
Registered User
 
Join Date: Mar 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Get Set Property value being lost asn187 ASP.NET 2.0 Professional 1 April 19th, 2007 03:09 PM
Why is Cache Property used in BizObject ? Scott663 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 March 8th, 2007 10:47 PM
Set accountExpires property r_ganesh76 C# 0 August 9th, 2006 01:16 AM
FreeThreadedDOMDocument <-> cache status Kabe Classic ASP XML 0 May 19th, 2005 08:35 AM
DataColumn .Unique property not being set gp_mk ADO.NET 3 November 4th, 2004 06:50 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.