ODS Excel creates a spreadsheet with all sheets selected

Meggles — Fri, 15 Dec 2017 13:37:45 GMT

Hello,

I am having trouble with xlsx files created using ODS excel. Each file is generated with all worksheets selected (so if you open the workbook all sheets are highlighted as though they have been selected together, but if you pick one the others go back to standard grey). This is causing problems for the team which use my file next.

Is there a way of getting ODS excel to not output like this? Code below.

* >> EXCEL << ;
     
ods listing close;
ods _all_ close;
ods results off;

options missing=0;

ods Excel file = "&reportOutputPath\&ReportName..xlsx" options (index='OFF');

	* Sheet 1 : Summary ;
	ods Excel
	   options(sheet_name="Summary"
				sheet_interval='none'
	            zoom='100'
				absolute_column_width = "10,10,12,14,12,10"
	        	 );
	run;

	proc odstext;
	  p "&ReportName. Summary" / style=[color=black font_style=italic font_size=11pt font_weight=bold];
	run;

	proc report data = xs_summary_table ;
		column agency cover number ;
		define agency				/ display 'Agency';
		define cover				/ display 'Cover';
		define number				/ display 'Count';
	run;

	title;
	footnote;

	* Dummy worksheet ;
	ods excel options(sheet_interval="table");
	ods exclude all;
		data _null_;
		file print;
		put _all_;
		run;
	ods select all;

	* Sheet 2 : Successes ;
	ods Excel
	   options(sheet_name="Successes"
				sheet_interval='none'
	            zoom='100'
				absolute_column_width = "14,14,14,15.5,14,18,14,14,14,16,14,14,14,14,14,14,16.5,14,14,14,20,20,20,20,20,20,20"
	        	 );
	run;

	proc odstext;
	  p "Successes" / style=[color=black font_style=italic font_size=11pt font_weight=bold];
	run;

	proc report data = good ;
		column agency cover event account_number expected actual ;
		define agency				/ display 'Agency';
		define cover				/ display 'Cover';
		define event				/ display 'Event';
		define account_number		/ display 'Account Number';
		define expected				/ display 'Expected Result';
		define actual				/ display 'Actual Result';
	run;

	title;
	footnote;

ods excel close;

Re: ODS Excel creates a spreadsheet with all sheets selected

SuzanneDorinski — Fri, 15 Dec 2017 21:16:33 GMT

It sounds like the worksheets are grouped. See the ungroup xlsx worksheets thread for a link to a hot fix.

topic Re: ODS Excel creates a spreadsheet with all sheets selected in ODS and Base Reporting

ODS Excel creates a spreadsheet with all sheets selected

Re: ODS Excel creates a spreadsheet with all sheets selected