tagsets.tableeditor - options to export to Excel

[pinklemon]

Hi,

I have a few questions regarding options on tableeditor. I hope someone can help clarify.

1. How I can make the excel_save_file option work? It seems that there is no Excel file being saved everytime I export it to Excel.

2. Is there a way to apply the options directly to Excel so that I don't have to export it after the HTML has been generated?

3. If I need to export several tables to Excel, is there a way to assign different sheet name for each table exported without prompts involved?

Below is a sample code I am testing:

     ods tagsets.tableeditor file="D:\Output\exporttoexcel.html"

          style=styles.mystyle

          options(excel_zoom="90"

                      excel_scale="90"

                      excel_autofilter="yes"

                      excel_frozen_headers="yes"

                      excel_orientation="landscape"

                      sheet_name="test"

                      auto_format="color1"

                      excel_save_file="D:\Output\exporttoexcel.xls"

                      open_excel="yes"

                    );

     proc print data=sashelp.class noobs;

     run;

     proc print data=sashelp.shoes noobs;

     run;

     ods tagsets.tableeditor close;

Appreciate your immediate response.

Thanks!

[Andre]

Pinklemon

i tried under excel 2010 and sas 9.3 with the corresponding tagsets for 9.3

I encounter many difficulties

1 the tagsets for 9.3 must be downloaded from sas page

2 i was working with firefox and not with IE  as default browser  and with the result viewer inside sas 9.3

  and  IE addon of firefox was not accepting the script of the activex control... of the by sas builded page

so i correct  to

                  IE as default for sas  to view ods results and no more result viewer!

and  this code was working  with the advice of allowing the activex script under IE

and excel was finally created

i don't think direct export to excel is possible  (as tableeditor was designed for html)

except an export from the  excelxp tagsets  by the mean of xml followed by an opening action of excel on this xml file.

Andre

NOTE in the code :  excel_save_file="D:\\temp\\exporttoexcel.xls"

double slash everywhere for IE

ods html close;

ods path sasuser.templat(update) sashelp.tmplmst(read);

ods tagsets.tableeditor file="D:\temp\exporttoexcel.html"

          style=styles.mystyle

          options(excel_zoom="90"

                      excel_scale="90"

                      excel_autofilter="yes"

                      excel_frozen_headers="yes"

                      excel_orientation="landscape"

                      sheet_name="test"

                      auto_format="color1"

                      excel_save_file="D:\\temp\\exporttoexcel.xls"

                      open_excel="yes"

                    );

    proc print data=sashelp.class noobs;  run;

    proc print data=sashelp.shoes noobs;  run;

    ods tagsets.tableeditor close;

[pinklemon]

Hi Andre,

Thank you for your inputs. I am currently using SAS 9.1.3 and Microsoft Excel 2003. I was able to save the Excel workbook as per your advice to put two slashes in my path. However, I am still thinking how I can export several tables in 1 worksheet and create multisheet at same time that contains several tables as well.

[Cynthia_sas]

Hi:

  Andre mentioned the possibility of using TAGSETS.EXCELXP. The advantages of this method are:

1) ODS creates output that conforms to the Office 2003 Spreadsheet Markup Language XML specification for multi-sheet workbooks

2) That means ODS can automatically create multi-sheet workbooks without making an HTML file first

3) TAGSETS.EXCELXP has an extensive set of suboptions that allow you to control printing, orientation, gridlines, etc (look in the log to see Doc='Help' documentation

4) You are creating an ASCII text file that is not an HTML file

The disadvantages of this method are:

1) By Microsoft design specs, you cannot use images, logos or graphs in the XML file

2) If your users want to see the file named as .XLS instead of .XML, you may have to open and resave the file from inside Excel

3) If you name the file with the .XLS extension in your SAS code, you are not creating a "true binary" Excel file, you are merely "fooling" the Windows registry into launching Excel when the file icon is clicked on.

Sample code is below. If you run it, you should see 3 worksheets in the workbook. Here's a user group paper that discusses some of the more popular suboptions.

http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

cynthia

title; footnote;

ods _all_ close;

** Open by doing a File-->Open from inside Excel;

ods tagsets.excelxp file='c:\temp\multsheet_wb.xml'

    style=sasweb

    options(doc='Help');

proc print data=sashelp.class(obs=5);

run;

proc print data=sashelp.shoes(obs=5);

run;

proc print data=sashelp.cars(obs=5);

run;

ods _all_ close;