Link to home
Start Free TrialLog in
Avatar of paulmcneil
paulmcneilFlag for United States of America

asked on

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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

What happens? An error? Nothing?
Also, do you have multiple tables built off the same cache?
ASKER CERTIFIED SOLUTION
Avatar of folderol
folderol

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of folderol
folderol

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

Tom.
Rory, as you might guess, I sometimes don't refresh! The PAQ I posted is one of yours, no surprise there :)
No worries, Tom. :)
Avatar of paulmcneil

ASKER

Thanks very much!