ODS TAGSETS TABLEEDITOR - PTDEST_RANGE Javascript Error

[baharvey78171]

I'm trying to create pivot tables within an existing MS Excel 2010 workbook on an existing spreadsheet. When ptdest_range='xx' is specified the Javascript code fails with 'Unable to get the PivotTableWizard property of the Worksheet class - Code:800A03EC'.  My objective is to create the pivot tables and pivot charts on the same spreadsheet.  Please advise how to do this properly.  An example or two would be helpful!!!

Here's the SAS code I'm using:

/* reference the tagset from support.sas.com */
Filename tpl url
"http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";

/* insert the tagset into the search path for ODS templates */
Ods path(Prepend) work.templat(update);
%include tpl;

ods excel file="c:\temp\temp.xlsx" options(sheet_name="shoe_report");

proc print data=sashelp.shoes noobs;
run;

ods excel close;

options noxsync noxwait;
ods tagsets.tableeditor file="c:\temp\PivotTable.js"                                                                                                                                      
/* remember to escape the backslashes */
  options(update_target="c:\\temp\\temp.xlsx"                                                                                                                                
    output_type="script"
    sheet_name="shoe_report"
    ptdest_range='i1'
    pivotpage='region'
    pivotrow="subsidiary"                                                                                                                                              
    pivotcol="product"                                                                                                                                             
    pivotdata="sales"  
    pivotdata_fmt="$#,###"
    pivot_format="light1"

);                                                                                                                                             

/* dummy output to trigger the file creation */                                                                                                                                                                                          
data _null_;                                                                                                                                                                          
 file print;
 put "test";                                                                                                                                                                             
run;                                                                                                                                                                            
                                                                                                                                                                  
ods tagsets.tableeditor close;
x "c:\temp\PivotTable.js";   

Here's the generated Javsscript code:

 var xl = new ActiveXObject("Excel.Application");
 xl.Visible = true;
 var wb = xl.Workbooks.Open("c:\\temp\\temp.xlsx");
 var sheet = wb.ActiveSheet;

 var pws = wb.ActiveSheet;
 var pnb=xl.ActiveWindow;
 var pvtTable = pws.PivotTableWizard(1, varSource=wb.sheets("shoe_report").Range("A1").CurrentRegion,TableDestination=sheet.Range("i1") ,pws.Range("A1"),"shoe_report_pivot",1,1);
 var ptformat=pws.PivotTables("shoe_report_pivot");
 pvtTable.PivotFields("SUBSIDIARY").Orientation = 1;
 pvtTable.PivotFields("PRODUCT").Orientation = 2;
 pvtTable.PivotFields("SALES").Orientation = 4;
 pvtTable.PivotFields("SUM OF SALES").Numberformat="$#,###";
 pvtTable.PivotFields("REGION").Orientation = 3;
 if (xl.Version > 11) {
 ptformat.TableStyle2 ="PivotStyleLight1";
}

 pws.Columns.Autofit;
 pws.Rows.Autofit;
xl.DisplayAlerts =0;
xl.DisplayAlerts = 1
xl.CutCopyMode = 0;
xl.EnableEvents = 0;
xl = null;

Any ideas?

[Cynthia_sas]

Hi:
I think you need to work with Tech Support. I never tamper with the JavaScript generated by TAGSETS.TABLEEDITOR and so I am not sure whether what you are trying to do will work.

cynthia