paulmcneil
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 ("PivotTab le11").Piv otCache.Co mmandText = "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.[Report ing Product Division]," _
& " tblLPG_YTD_COUNTRY.[Report ing Business Segment]," _
& " tblLPG_YTD_COUNTRY.[Report ing Product Group]," _
& " tblLPG_YTD_COUNTRY.[Commer cial Product Line]," _
& " tblLPG_YTD_COUNTRY.[Commer cial Product Classification]," _
& " tblLPG_YTD_COUNTRY.[Produc t Number]," _
& " tblLPG_YTD_COUNTRY.[Prior Quantity]," _
& " tblLPG_YTD_COUNTRY.[Prior Amount USD]," _
& " tblLPG_YTD_COUNTRY.[Curren t Quantity]," _
& " tblLPG_YTD_COUNTRY.[Curren t Amount USD]," _
& " tblLPG_YTD_COUNTRY.[Prior ASP]," _
& " tblLPG_YTD_COUNTRY.[Curren t ASP]," _
& " tblLPG_YTD_COUNTRY.[Prior ASP * Current Quantity]," _
& " tblLPG_YTD_COUNTRY.[AMT From Price]," _
& " tblLPG_YTD_COUNTRY.[Matche d Revenue]," _
& " tblLPG_YTD_COUNTRY.[Change in ASP]" _
& " FROM tblLPG_YTD_COUNTRY"
xlwb.Sheets(1).PivotTables ("PivotTab le11").Ref resh
xlwb.Save
Dim xlwb As Workbook
Set xlwb = ActiveWorkbook
xlwb.Sheets(1).PivotTables
& " 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.[Report
& " tblLPG_YTD_COUNTRY.[Report
& " tblLPG_YTD_COUNTRY.[Report
& " tblLPG_YTD_COUNTRY.[Commer
& " tblLPG_YTD_COUNTRY.[Commer
& " tblLPG_YTD_COUNTRY.[Produc
& " tblLPG_YTD_COUNTRY.[Prior Quantity]," _
& " tblLPG_YTD_COUNTRY.[Prior Amount USD]," _
& " tblLPG_YTD_COUNTRY.[Curren
& " tblLPG_YTD_COUNTRY.[Curren
& " tblLPG_YTD_COUNTRY.[Prior ASP]," _
& " tblLPG_YTD_COUNTRY.[Curren
& " tblLPG_YTD_COUNTRY.[Prior ASP * Current Quantity]," _
& " tblLPG_YTD_COUNTRY.[AMT From Price]," _
& " tblLPG_YTD_COUNTRY.[Matche
& " tblLPG_YTD_COUNTRY.[Change
& " FROM tblLPG_YTD_COUNTRY"
xlwb.Sheets(1).PivotTables
xlwb.Save
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For the record, I tested for myself both solutions and as far as I can tell, they are correct.
Tom.
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. :)
ASKER
Thanks very much!
Also, do you have multiple tables built off the same cache?