Date:         Thu, 28 Sep 2000 12:48:45 GMT
Reply-To:     John@Iwaniszek.com
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         John Iwaniszek <John@IWANISZEK.COM>
Organization: Road Runner - NC
Subject:      Re: writing to a different worksheet in an excel file than the
              first one
Content-Type: text/plain; charset=us-ascii

Sebastian wrote:
>
> Hi,
>
> i need to export data to an excel-file, how can i write into the second,
> third, ... worksheet in the file? I did not find any useful option...
>
> Regards
> Sebastian


I recommend David Ward's excel macros available at sashelp.com (when he
completes his migration).  they provide some good basic support for
working with excel spreadsheets.

Of these I have used: x_run, x_saveas, x_table, s_save, and x_exit.

Here is a code fragment that creates new worksheets and puts them in the
proper order.

        %do _lexx=1 %to &numits;

                %if &_lexx>3 %then %do;
                        data
                                _null_
                                ;

                                file ecmds;

                                put "[WORKBOOK.INSERT(1)]";

change='[WORKBOOK.ADD("sheet'||"&_lexx"||'",,'||"%eval(&_lexx+1)"||')]';
                                put change;

                        run ;
               %end;

        %end;



Here is some code that will format columns in a worksheet by altering
column and cell properties:

        %do _lexx=1 %to &numits;/**/
                data
                        _null_
                        ;
                        file
                                ecmds
                                ;

                        put
"[Workbook.select(""sheet&_lexx"",""sheet&_lexx"")]";
                        put '[SELECT.SPECIAL(5)]';

                        put '[ALIGNMENT( 3, FALSE, 2)]';
                        put '[ROW.HEIGHT(72,,FALSE)]';

                        put '[select( "c4" )]';
                        put '[FORMAT.FONT("Arial", 8)]';
                        put '[column.width(11,"c4",FALSE)]';

                        put '[select( "c5" )]';
                        put '[FORMAT.FONT("Arial", 8)]';
                        put '[column.width(11,"c5",FALSE)]';

                        put '[select( "c6" )]';
                        put '[FORMAT.FONT("Arial", 8)]';
                        put '[column.width(11,"c6",FALSE)]';

                        put '[select( "c7" )]';
                        put '[FORMAT.FONT("Arial", 8)]';
                        put '[column.width(11,"c7",FALSE)]';

                        put '[select( "r1")]';
                        put '[ALIGNMENT( 3, FALSE, 3)]';
                        put '[ROW.HEIGHT(20,,FALSE)]';
                        put '[FORMAT.FONT("Arial", 10)]';

                        put '[select( "r1:c1")]';

        %end;


--
John Iwaniszek

Statistical Programming Manager
Stat-Tech Services, LLC

919 571 6444

Developers of the Macro Reporting System - Delivering
Statistical reports in ASCII, RTF, and HTML

http://www.StatTechServices.com