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;

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=A0am, 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_;
> =A0 =A0length fid rc start stop time 8.;
> =A0 =A0fid=3Dfopen('sas2xl','s');
> =A0 =A0 =A0 =A0 if (fid le 0) then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 do;
> =A0 =A0 =A0 =A0 =A0 =A0rc=3Dsystem('start excel');
> =A0 =A0 =A0 =A0 =A0 =A0start=3Ddatetime();
> =A0 =A0 =A0 =A0 =A0 =A0stop=3Dstart+10;
> =A0 =A0 =A0 =A0 =A0 =A0do while (fid le 0);
> =A0 =A0 =A0 =A0 =A0 =A0 =A0fid=3Dfopen('sas2xl','s');
> =A0 =A0 =A0 =A0 =A0 =A0 =A0time=3Ddatetime();
> =A0 =A0 =A0 =A0 =A0 =A0 =A0if (time ge stop) then fid=3D1;
> =A0 =A0 =A0 =A0 =A0 end;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 end;
> =A0 =A0 =A0 =A0 rc=3Dfclose(fid);
> run;
> data _null_;
> =A0 =A0 =A0 =A0 file sas2xl;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 put '[file.close(false)]';
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 put '[new(1)]';
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 put '[error(false)]';
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 put '[select("r1c1:r200c200")]';
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 put '[format.number("@")]';
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 put =A0&save.;
> run;
> data _null_;
> =A0 =A0 =A0 =A0 file sas2xl;
> =A0 =A0 =A0 =A0 put '[workbook.next()]';
> =A0 =A0 =A0 =A0 put '[workbook.insert(3)]';
> run;
> filename xlmacro dde 'excel|macro1!r1c1:r200c1' notab;
> data _null_;
> =A0 =A0 =A0 =A0 file xlmacro;
> =A0 =A0 =A0 =A0 put '=3Dworkbook.name("sheet1","data")';
> =A0 =A0 =A0 =A0 put '=3Dhalt(true)';
> =A0 =A0 =A0 =A0 put '!dde_flush';
> =A0 =A0 =A0 =A0 file sas2xl;
> =A0 =A0 =A0 =A0 put '[run("macro1!r1c1")]';
> run;

Arthur,

thank you for your answer! But, for whatever reason, it still doesn't
work...
I followed the guide provided by Vyverman (or at least I think so). I
can start excel, I can add the extra sheet, move it and change it to a
macrosheet, and in the last step, SAS writes the following in r1c1 in
the sheet called macro1: =workbook.name("sheet1","Works Fine Now") and
in r2c1: =HALT(TRUE) and thats it! I'm not a excel expert (or a SAS
expert) but I the secound last put in the last step should run the
macro?

By the way sometimes I'm getting this in my log, and sometimes not:

ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the data
step program.
            Aborted during the EXECUTION phase.


Well, below you can see the light modified code I'm using now:

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 '[workbook.next()]';
	put '[workbook.insert(1)]';
	put '[workbook.move("sheet1","existing workbook.xls",1)]';
run;
data _null_;
	file sas2xl;
	put '[workbook.next()]';
	put '[workbook.insert(3)]';
	put '[workbook.move("macro1","existing workbook.xls",1)]';
run;
filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=200;
data _null_;
	file xlmacro;
	put '=workbook.name("sheet1","Works Fine Now")';
	put '=halt(true)';
	put '!dde_flush';
	file sas2xl;
	put '[run("macro1!r1c1")]';
	put '[error(false)]';
run;

Thanks!

Hi Hannes

I cannot offer much help, but I think that it is a question of the Excel 
version you use. I had a program that used this functionality (copied from 
Vyverman) and it worked like a dream with Excel 2000, but I have never been 
able to make it work under Excel 2003.

In general, it seems as if we loose some DDE functionality for each new 
version of MsOffice. Other functionalities lost are AppMinimize/AppMaximize 
and DocMinimize/DocMaximize in the communication with Word 2003. (The SAS 
version is of course irrelevant because we only use trivial put statements).

My OS is WinXP.

Anders


"H Engberg" <hannesengberg@gmail.com> skrev i en meddelelse 
news:01f32103-c134-47b2-9087-183527dcec6d@30g2000yqm.googlegroups.com...
>
> Arthur,
>
> thank you for your answer! But, for whatever reason, it still doesn't
> work...
> I followed the guide provided by Vyverman (or at least I think so). I
> can start excel, I can add the extra sheet, move it and change it to a
> macrosheet, and in the last step, SAS writes the following in r1c1 in
> the sheet called macro1: =workbook.name("sheet1","Works Fine Now") and
> in r2c1: =HALT(TRUE) and thats it! I'm not a excel expert (or a SAS
> expert) but I the secound last put in the last step should run the
> macro?
>
> By the way sometimes I'm getting this in my log, and sometimes not:
>
> ERROR: DDE session not ready.
> FATAL: Unrecoverable I/O error detected in the execution of the data
> step program.
>            Aborted during the EXECUTION phase.
>
>
> Well, below you can see the light modified code I'm using now:
>
> 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 '[workbook.next()]';
> put '[workbook.insert(1)]';
> put '[workbook.move("sheet1","existing workbook.xls",1)]';
> run;
> data _null_;
> file sas2xl;
> put '[workbook.next()]';
> put '[workbook.insert(3)]';
> put '[workbook.move("macro1","existing workbook.xls",1)]';
> run;
> filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=200;
> data _null_;
> file xlmacro;
> put '=workbook.name("sheet1","Works Fine Now")';
> put '=halt(true)';
> put '!dde_flush';
> file sas2xl;
> put '[run("macro1!r1c1")]';
> put '[error(false)]';
> run;
>
> Thanks! 

oh, okey, I have excel 2003... But does this mean that I must rename
my worksheets after I have created them myself? Or is it possible to
change the excel version without reinstalling?

Thanks!
Hannes

At least that was what I did. My program is run twice a year and only 
comprises some 10 work sheets. If it is a frequent task with many sheets you 
might consider writing a VBA macro.

Anders


"H Engberg" <hannesengberg@gmail.com> skrev i en meddelelse 
news:81fd5278-d624-40f5-9733-d966f233409b@30g2000yqm.googlegroups.com...
> oh, okey, I have excel 2003... But does this mean that I must rename
> my worksheets after I have created them myself? Or is it possible to
> change the excel version without reinstalling?
>
> Thanks!
> Hannes 

Hannes,

With the file already open in Excel, I was able to run the following,
successfully, on my computer (Windows Server 2003, Excel 2003, SAS
9.1.3(m3):

*Macro sheet for rename**;

filename sas2xl dde 'excel|system';
data _null_;
        file sas2xl;
        put '[workbook.next()]';
        put '[workbook.insert(3)]';
run;

filename m1  dde 'excel|macro1!r1:r50';


**New Sheet Name**;
data _null_;
   NName  =3D '""'||'NewName'||'""';
   CALL SYMPUT('NName',NName);
run;
**Rename and close**;
data _null_;
   file m1 notab;
   renm =3D "=3Dworkbook.name(""Sheet1"",&NName)";
   put renm;
   put '=3DHalt(True)';
   put '!DDE_FLUSH';

   file sas2xl;
   put '[RUN("Macro1!R1C1")]';
   put '[ERROR(False)]';
   put '[WORKBOOK.DELETE("Macro1")]';

   put '[SAVE()]';
   put '[QUIT()]';
run;

Of course, you should be able to simply add that code to the code you
are using to open Excel.

Art
------------
On Oct 27, 5:32=A0am, H Engberg <hannesengb...@gmail.com> wrote:
> oh, okey, I have excel 2003... But does this mean that I must rename
> my worksheets after I have created them myself? Or is it possible to
> change the excel version without reinstalling?
>
> Thanks!
> Hannes