*************************************************************************;
* Macroexporterexisting.sas                                             *;
* Version 1.1                                                           *;
* Date January 18 2002                                                  *;
* Purpose:  to export multiple SAS datasets to multiple sheets in excel *;
* THIS PROGRAM UPDATES AN EXISTING WORKBOOK, TO CREATE A NEW WORKBOOK   *;
* MAKE SURE YOU USE MACROEXPORTER1                                      *;
* MAKE SURE YOU MAKE ALL CHANGES WHERE YOU SEE THE ARROWS >>>>>>>>      *;
* SCAN THE PROGRAM FOR THESE                                            *;
*                                                                       *;
* Limitations:                                                          *;
* Program cannot be run on remote connect or internet                   *;
* servers where there is no display terminal                            *;
* You will need to change the excel.exe location depending on           *;
* where you installed Microsoft office                                  *;
* While this was customized for excel, it could be modified             *;
* for Quattro Pro but not Lotus 1-2-3 which does not support DDE        *;
* Dataset names WORK.SETUP or WORK.DELETER may not be used              *;
*                                                                       *;
*************************************************************************;

dm wpgm 'clear log' wpgm;

* These options are used for debugging *;
* You can turn these off if you wish   *;
options mprint macrogen symbolgen;

*These options must remain in place    *;
*in order for the program to run       *;
options noxwait noxsync;

********************************************;
* Now to start the macro                   *;
* the first loop here takes all the        *;
* dataset names and figures out how many   *;
* there are and creates a counter          *;
* see the actual macro call for the        *;
* explanation of the parameters            *;
********************************************;
 %macro exloop(dir1,savefile,deltemp,wrkrep);
 %put &dir1 &savefile &deltemp &wkrep;
data _null_;
set setup end=last;
call symput('dsnamem'||left(_n_),trim(dsname1));
call symput('dsnamem2'||left(_n_),trim(dsname2));
if last then call symput('counter',_n_);
run;

*************************************************;
* this macro loop goes thru the counter and     *;
* exports the sas datasets to individual xls    *;
* files                                         *;
*************************************************;

%do i= 1 % to &counter;
%put &&dsnamem&i;
proc export data=&&dsnamem&i dbms=excel97 
outfile="&dir1&&dsnamem2&i...xls" replace;
run;
%end;
 
*********************************************;
* Then we start the DDE loop itself         *;
* NOTE THE LOCATION OF MICROSOFT OFFICE     *;
* YOU MAY NEED TO CHANGE THIS TO MATCH YOUR *;
* LOCATION                                  *;
* Subsequent loops insert into the          *;
* XLS file and saves the XLS file           *;
* when complete.                            *;
*********************************************;


filename cmds dde 'excel|system';
***************************************************************;
* Now we start up microsoft excel                             *;
* for Office 97 and 2000 use                                  *;
* x 'c:\progra~1\micros~2\office\excel.exe                    *;
* for Office XP users use                                     *;
* x 'c:\progra~1\micros~2\office10\excel.exe                  *;
***************************************************************;
*>>>>>>>>>>> change the location of the excel.exe  here <<<<< *;
	x 'c:\progra~1\micros~2\office10\excel.exe';
	* let fullname=&dir1&&dsnamem2&i...xls;
	%let opener=%str(%'[open("&savefile")]%');
	data startup;
	x=sleep(5);
	run; quit;
	data _null_;
	file cmds;
	put %unquote(&opener);
	run;
	quit;
	%end;
*************************************************************;
* Now we export and replace the sheet                       *;
* The first routine deletes the duplicate sheet if it       *;
* is present and the macro variable is set to replace       *;
* The second routine proceeds to insert the sheets          *;
*************************************************************;

%do i= 1 % to &counter;
    %if &wrkrep=replace %then %do;
	    %let delsheet=%str(%'[workbook.delete("&&dsnamem2&i")]%');
	    data _null_;
        file cmds;
		put '[ERROR("FALSE")]';
	    put %unquote(&delsheet);
	    %end;
  %let insname=&dir1&&dsnamem2&i...xls;
  %let inserter=%str(%'[workbook.insert("&insname")]%');
    data _null_;
	file cmds;
	put %unquote(&inserter);
	%end;

%end;

* This step saves the file as a new multisheet XLS file *;
data _null_;
%let saveas=%str(%'[save.as("&savefile",1)]%');
file cmds;
put '[ERROR("FALSE")]';
put %unquote(&saveas);
put '[quit()]';
run; quit;

* Now to build a bat file to clean up the temporary files *;
%if &deltemp=yes %then %do;
	%let file1a=del1.bat;
	%let file1b=&dir1;
	%let filecmd=&dir1&file1a;


	filename del1 "&filecmd";
	data deleter; set setup;
	delvar='"'||left(trim("&dir1"))||left(trim(dsname2))||'.xls'||'"';
	delvar2='del '||delvar;
	file del1;
	put delvar2;
	run;
	quit;

* Then delete the intermediate xls files          *;
* If you choose not to delete the files it writes *;
* a message to the log for the location           *;

	data _null_;
	call system(%unquote(%str(%'&filecmd%')));
	run; quit;
	%end;
	%else %do;
	%put "Temporary files are still in &dir1";
	%end;




%mend exloop;  

******************************************************************;
* This part loads the datasets that are turned into individual   *;
* sheets in the excel workbook.    All datasets names MUST       *;
* have 2 level names.  Temporary datasets should use a WORK.     *;
* prefix, such as WORK.TEMP1.  DATASET NAMES SHOULD NOT          *;
* BE THE SAME AS THE ORIGINAL XLS DATASET or you will replace    *;
* the dataset AND MAY LOCK THE PROGRAM.  Use Macroexporter1 for  *;
* this purpose.                                                  *;
* You may not use the names WORK.DELETER or WORK.SETUP as        *;
* these are used by the macro itself                             *;
******************************************************************;

data setup;
length dsname1 $ 35;
input dsname1 $;
* >>>>>>>> Change the dataset names here after the cards statement *;
cards;
sashelp.class
sashelp.buy
sashelp.air
;

data setup; set setup;
dsname2=scan(dsname1,2);
run; quit;



**********************************************************;
* Now we call the macro here                             *;
* The first parameter tells where to store the temporary *;
* xls files,  the 2nd parameter is where the final       *;
* dataset should be stored, the 3rd parameter controls   *;
* whether the temporary xls files should be deleted at   *;
* completion or not.  Yes means delete temporary xls     *;
* files, no means not to delete the temporary xls files  *;
* You should specify directories that exist, the program *;
* will not make them for you.                            *;
* The 4th parameter controls whether you want to replace *;
* an existing sheet if it is present and is the same     *;
* name as one of the SAS datasets                        *;
* If it is set to replace, it replaces the sheetname     *;
* If it is NOT set to replace, then it appends a number  *;
* to the sheetname when it adds it                       *;
**********************************************************;

* >>>>>>>> Change the parameters here <<<<<<<<<<<<<<<<<<<*;

%exloop(c:\export2\,c:\export2\baseball.xls,yes,replace);

run;