topic SPWA tagsets.ExcelXP not opening in Excel in Developers

SPWA tagsets.ExcelXP not opening in Excel

droog — Mon, 17 Mar 2014 19:03:00 GMT

I have a simple STP, code below, invoked from the SPWA which is writing tagsets.ExcelXP output. I've tried different combos of the filename suffix, setting the MIME via stpsrv and appsrv, specifying "file=_webout", nothing works. The output only displays in a new browser window ( the XML is 100% correct, I can copy / save and open successfully with Excel 2010 ). Searching ODS and STP communities, been beating my head against the wall toooooo long, any insight will be most appreciated.

The browser is IE v7 ( I know, corp standard!! ).

%let _odsdest = tagsets.excelXP;

%let _odsoptions =;

%let _odsstyle = sasweb;

%let dto = %sysfunc(putn("&dt"d,yymmddd10.));

%let rv = %sysfunc( stpsrv_header('Content-type','application/vnd.ms-excel') );

%let rv = %sysfunc( stpsrv_header('Content-disposition',"attachment; filename=mor_missing_system_userids_&dto..xml"));

%STPBEGIN;

<snip>

* End EG generated code (do not edit this line);

<snip>

ods tagsets.excelxp

options ( sheet_name = 'Overall' absolute_column_width='30,10' zoom='80');

ods listing close;

ods noresults;

proc print data = system_nm noobs label;

label system_nm = 'System NM'

cnt = 'Count';

format cnt comma12.;

run;

ods tagsets.excelxp style=statistical

options ( sheet_name = 'none'

sheet_label = ' '

sheet_interval = 'Bygroup'

absolute_column_width = '10,10,15,25,25,30,7'

embedded_titles = 'yes'

autofilter = 'yes'

zoom = '80'

);

proc report data = warning_defaulted_srf_d nowindows ;

by system_nm;

columns employee_project_nm employee_pillar_nm employee_department_nm reporting_entity_nm

event_userid comment cnt;

<snip>

run;

ods tagsets.excelxp close;

ods listing;

ods results;

Re: SPWA tagsets.ExcelXP not opening in Excel

Quentin — Mon, 17 Mar 2014 19:19:42 GMT

Quick glance, looks reasonable.

Why the second line? :

%let _odsoptions= ;

Maybe that is over-writing some options set by SPWA.

Re: SPWA tagsets.ExcelXP not opening in Excel

droog — Mon, 17 Mar 2014 19:25:20 GMT

Quentin: I commented out the _odsoptions line, same thing...

Re: SPWA tagsets.ExcelXP not opening in Excel

Quentin — Mon, 17 Mar 2014 19:26:00 GMT

My code for stpsrv_header doesn't have thoe quote marks in your code. Don't need them in macro %LET statement.

That could definitely be the problem. Mine looks like:

  %let rc=%sysfunc(stpsrv_header(Content-type,application/vnd.ms-excel));
  %let rc=%sysfunc(stpsrv_header(Content-disposition,attachment%str(;) filename=ReportFile.xls));

Re: SPWA tagsets.ExcelXP not opening in Excel

droog — Mon, 17 Mar 2014 19:31:18 GMT

Bingo. Initially I had the STPSRV invocations in a data _null_, made them macro and forgot to take off the quotes....

Thanks much Quentin.