[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

Trying to change Excel 2003 Pivotcache CommandText

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"
     

xlwb.Sheets(1).PivotTables("PivotTable11").Refresh
xlwb.Save
0
paulmcneil
Asked:
paulmcneil
  • 3
  • 2
1 Solution
 
Rory ArchibaldCommented:
What happens? An error? Nothing?
Also, do you have multiple tables built off the same cache?
0
 
folderolCommented:
See this PAQ....

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24112710.html

and this programmer to programmer site url...
http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html

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.

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

Tom.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
folderolCommented:
Rory, as you might guess, I sometimes don't refresh! The PAQ I posted is one of yours, no surprise there :)
0
 
Rory ArchibaldCommented:
No worries, Tom. :)
0
 
paulmcneilAuthor Commented:
Thanks very much!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now