Trying to change Excel 2003 Pivotcache CommandText

Posted on 2010-01-06
Last Modified: 2012-05-08
Why won't the following code work to change the CommandText of an Excel 2003 Pivotcache?
Dim xlwb As Workbook

Set xlwb = ActiveWorkbook

xlwb.Sheets(1).PivotTables("PivotTable11").PivotCache.CommandText = "SELECT tblLPG_YTD_COUNTRY.ID," _
      & " tblLPG_YTD_COUNTRY.[Ship To LED Region]," _
      & " tblLPG_YTD_COUNTRY.[Ship To LED Region 2]," _
      & " tblLPG_YTD_COUNTRY.[Ship To LED Sub Region]," _
      & " tblLPG_YTD_COUNTRY.[Ship To Country]," _
      & " tblLPG_YTD_COUNTRY.[Bill To Sales Channel]," _
      & " tblLPG_YTD_COUNTRY.[Reporting Product Division]," _
      & " tblLPG_YTD_COUNTRY.[Reporting Business Segment]," _
      & " tblLPG_YTD_COUNTRY.[Reporting Product Group]," _
      & " tblLPG_YTD_COUNTRY.[Commercial Product Line]," _
      & " tblLPG_YTD_COUNTRY.[Commercial Product Classification]," _
      & " tblLPG_YTD_COUNTRY.[Product Number]," _
      & " tblLPG_YTD_COUNTRY.[Prior Quantity]," _
      & " tblLPG_YTD_COUNTRY.[Prior Amount USD]," _
      & " tblLPG_YTD_COUNTRY.[Current Quantity]," _
      & " tblLPG_YTD_COUNTRY.[Current Amount USD]," _
      & " tblLPG_YTD_COUNTRY.[Prior ASP]," _
      & " tblLPG_YTD_COUNTRY.[Current ASP]," _
      & " tblLPG_YTD_COUNTRY.[Prior ASP * Current Quantity]," _
      & " tblLPG_YTD_COUNTRY.[AMT From Price]," _
      & " tblLPG_YTD_COUNTRY.[Matched Revenue]," _
      & " tblLPG_YTD_COUNTRY.[Change in ASP]" _
      & " FROM tblLPG_YTD_COUNTRY"

Question by:paulmcneil
    LVL 85

    Expert Comment

    by:Rory Archibald
    What happens? An error? Nothing?
    Also, do you have multiple tables built off the same cache?
    LVL 19

    Accepted Solution

    See this PAQ....

    and this programmer to programmer site url...

    The EE page just states that you can't write to the .commandtext property if more than one pivottable is using the pivotcache.  Don't use the option to base a pivottable on another pivottable, and you will be able to edit the pivottable.pivotcache.commandtext property.

    The Programmer 2 Programmer Forum gives a work-around, because the real issue is that the query is an ODBC connection.  Temporarily changine it to OLEDB allows you to write to the commandtext property, but then you have to restore the .connection property back to its original ODBC syntax string. That's kind of a clunky work-around if you are asking me.

    LVL 19

    Expert Comment

    For the record, I tested for myself both solutions and as far as I can tell, they are correct.

    LVL 19

    Expert Comment

    Rory, as you might guess, I sometimes don't refresh! The PAQ I posted is one of yours, no surprise there :)
    LVL 85

    Expert Comment

    by:Rory Archibald
    No worries, Tom. :)

    Author Closing Comment

    Thanks very much!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now