Date:         Tue, 26 Oct 2010 13:13:40 -0700
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: rename worksheets
Content-Type: text/plain; charset=ISO-8859-1

Hannes,

You appear to be missing a couple of the line that Koen suggested in
one of his papers on the topic:
http://www2.sas.com/proceedings/sugi26/p011-26.pdf

Renaming is covering in section 8.

Art
---------
On Oct 26, 11:20 am, H Engberg <hannesengb...@gmail.com> wrote:
> hi,
>
> I know, this question have been asked a billion times, but I must ask
> once more...
> I need to rename my worksheets, Sheet1, Sheet2... isn't good enough.
> the code I'm using is the following, however 'Sheet1' is never renamed
> in this case. I can open excel with the first data step, then do some
> changes and I save the workbook. Please, someone tell what I have done
> wrong!
>
> Hannes
>
> Code:
>
> options noxsync noxwait xmin;
> filename sas2xl dde 'excel|system';
> data _null_;
>    length fid rc start stop time 8.;
>    fid=fopen('sas2xl','s');
>         if (fid le 0) then
>                 do;
>            rc=system('start excel');
>            start=datetime();
>            stop=start+10;
>            do while (fid le 0);
>              fid=fopen('sas2xl','s');
>              time=datetime();
>              if (time ge stop) then fid=1;
>           end;
>                 end;
>         rc=fclose(fid);
> run;
> data _null_;
>         file sas2xl;
>                 put '[file.close(false)]';
>                 put '[new(1)]';
>                 put '[error(false)]';
>                 put '[select("r1c1:r200c200")]';
>                 put '[format.number("@")]';
>                 put  &save.;
> run;
> data _null_;
>         file sas2xl;
>         put '[workbook.next()]';
>         put '[workbook.insert(3)]';
> run;
> filename xlmacro dde 'excel|macro1!r1c1:r200c1' notab;
> data _null_;
>         file xlmacro;
>         put '=workbook.name("sheet1","data")';
>         put '=halt(true)';
>         put '!dde_flush';
>         file sas2xl;
>         put '[run("macro1!r1c1")]';
> run;