<=== Page: 1 === Emits === primary === 24Jul2006 09:25:02 ===>


Hello, I have a question about populating data into an Excel sheet.  I am generally familiar with ODE.  Is it possible to create a new tab in an excel sheet through a SAS syntax?  Thank you for your advice on this.



Kaz



<=== Page: 2 === Peter Ruzsa === emailed w/answer === 25Jul2006 08:54:53 ===>

Good Morning Kaz,

Indeed you can,  you can use the workbook.insert to insert a new sheet into an existing workbook.

Here is a sample that shows several worksheet functions...

**************************************************************************
****;
*first we open excel, assuming program files\microsoft office\office\excel.exe*;
**************************************************************************
*****;

options noxwait noxsync;
x "C:\progra~1\micros~2\Office10\EXCEL.EXE"  ; run; quit;
**************************************************************************
****;
* setup excel to receive word basic macro commands *;
**************************************************************************
****;

filename cmds dde 'excel|system';

* then we save the open blank book to a file *;
* this code is useful when you want to start and
  create a new book *;
* the code is currently commented out in this example *;

/*data _null_;
file cmds;
put '[save.as("c:\testsas\book2.xls")]';
run; quit; */

* open the existing workbook *;

data _null_;
file cmds;
put '[open("c:\testsas\book2.xls")]';
run; quit;

*  then we write to the first sheet in the book *;

filename excelw dde 'Excel|c:\testsas\[book2.xls]Sheet1!R1C1:R19C5';
data _null_;  set sashelp.class;
file excelw;
put name '09'x sex;
run;

* now lets switch to the second sheet *;

filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[workbook.activate("sheet2")]';
run;  quit;

* now lets write to that second sheet *;


filename excelw dde 'Excel|c:\testsas\[book2.xls]Sheet2!R1C1:R19C5';
data _null_;  set sasuser.baseball;
file excelw;
put name '09'x division;
run;

* now lets insert another sheet *;

filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[workbook.insert(1)]';
run;  quit;

* now lets write to that new inserted sheet *;


filename excelw dde 'Excel|c:\testsas\[book2.xls]Sheet4!R1C1:R19C5';
data _null_;  set sasuser.baseball;
file excelw;
put name '09'x league;
run; quit;


* now lets delete another sheet *;

filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[workbook.delete("sheet1")]';
run;  quit;

* lets save the workbook and then exit *; filename cmds dde 'excel|system'; data _null_; file cmds; put '[save()]'; put '[quit()]'; run;