ODS msoffice2k to excel worksheets

[Bill]

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;

[Cynthia_sas · Accepted solution]

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;

[Bill]

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

[Cynthia_sas]

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

[Bill]

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;    

[Cynthia_sas]

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

[GeorgeSAS]

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;

[Cynthia_sas]

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