topic Re: ODS msoffice2k to excel worksheets in ODS and Base Reporting

ODS msoffice2k to excel worksheets

Bill — Tue, 14 Apr 2015 15:07:39 GMT

How does one direct outputs to and name worksheets with the ods msofffice2k?

Hopefully the code snippet below will explain my question. Thanks, Bill

ods tagsets.msoffice2k file='c:\temp\test.xls;

under first worksheet tab

proc tabulate ...

run;

under second worksheet tab

proc tabulate ...

run;

ods _all_ close;

Re: ODS msoffice2k to excel worksheets

Cynthia_sas — Tue, 14 Apr 2015 16:42:03 GMT

Hi:

With ODS MSOFFICE2K, you only have 1 workbook with 1 worksheet. So there won't be multiple worksheets to name. Generally, with MSOFFICE2K as the destination, when you specify FILE=, the name of your file (in your example, test.xls) would be the name of the sheet (test). If you want to make multi-sheet workbooks, TAGSETS.EXCELXP will give you that capability.

Cynthia

ods tagsets.excelxp file='c:\temp\namesheets.xls' options(sheet_name='Wombat') style=sasweb;
proc print data=sashelp.class;
title '1) first';
run;

ods tagsets.excelxp options(sheet_name='Koala');
proc print data=sashelp.shoes(obs=4);
var region product sales inventory returns;
title '2) second';
run;
ods _all_ close;

Re: ODS msoffice2k to excel worksheets

Bill — Wed, 15 Apr 2015 14:37:01 GMT

Thank you Cynthia.

While it does do exactly as you say, I've now found that using a customized style results in an excel error of some kind (can't get at the log of that error) when I try to open the xls file.

Bill

Re: ODS msoffice2k to excel worksheets

Cynthia_sas — Thu, 16 Apr 2015 01:41:42 GMT

Hi:

Usually, that kind of error means there is something wrong with your style specification that Excel does not like. The error is buried in some temp file that Excel builds but it is generally an XML error. I recommend backing off the custom style and taking a slower approach to changing things -- like make one style change at a time until you find the one that breaks it. You are not actually creating a proprietary .XLS file with TAGSETS.EXCELXP. You are actually creating an XML file that Excel knows how to open. So the good news is that as soon as you fix the style issue, Excel will open the XML file. The bad news is that you have to fix the style issue.

cynthia

Re: ODS msoffice2k to excel worksheets

Bill — Thu, 16 Apr 2015 16:01:20 GMT

The more I have to interact with xl, the more I'd like to abolish it!

The EXCELXP tagset works with the following style changes for a number of parent styles, but not for styles.theme - although the original styles.theme works. Just "copying" theme to mytheme causes a failure!

proc template;

define style mytheme;

parent=styles.theme ;

replace Body from Document

"Undef margins so we get the margins from the printer or SYS option"

bottommargin = 0.19in

topmargin = 0.19in

rightmargin = 0.20in

leftmargin = 0.20in;

replace Table from output/

rules = none

frame = void

bordercolor = #f7f7f7

borderwidth = 1px; */

end;

run;

Re: ODS msoffice2k to excel worksheets

Cynthia_sas — Thu, 16 Apr 2015 18:48:09 GMT

Hi:

The REPLACE statement went away in SAS 9.2, so that is one possible problem. Then, you are doing more in your code than just "copying" styles.theme to another style. You are making style changes that may or may not be supported. For example, I would never try rules=none and frame=void because those are directly contrary to setting a bordercolor and borderwidth. You've turned off the outer border with frame=void (or you're trying to), so what is the point of bordercolor? And, it was my understanding that the margin settings only worked for "paged" destinations like PDF and RTF, so I would not expect them to work with TAGSETS.EXCELXP. TAGSETS.EXCELXP has its own controls for printing and they do not involve changing the margins, as I remember.

cynthia

Re: ODS msoffice2k to excel worksheets

GeorgeSAS — Fri, 28 Apr 2017 21:11:24 GMT

Hello Cynthia,

TAGSETS.EXCELXP can't output graph. what I can do?

ods listing close;
ods tagsets.excelxp file='c:\temp\zzz66.xls'
style=sasweb;
ods tagsets.excelxp options(sheet_interval="none");
ods tagsets.excelxp options( embedded_titles='yes' embedded_footnotes='yes' );

title "Where is my plot?";
proc sql;
select * from sashelp.class
quit;

proc sgplot data=sashelp.class;
title 'sashelp--class';
scatter x=height y=weight / group=sex;
run;

ods tagsets.excelxp close;
ods listing;
run;

Re: ODS msoffice2k to excel worksheets

Cynthia_sas — Sat, 29 Apr 2017 13:35:45 GMT

This post from 2017 has been answered here:
https://communities.sas.com/t5/ODS-and-Base-Reporting/Can-tagsets-excelxp-output-plot/td-p/354594
cynthia