You could use DDE to save the file as an exel file (saving space) and also to add the imagine to anywhere in each tab or to the required tabs. Remove the loop and pick the tabs required using the [workbook.activate("SUMMARY")] function.
You will need to create a template excel file which contains the imagine(s).
Then use the following code....
%let logos= ;
%let Report = ;
%let loop_num = ;
/* open up excel (standard DDE code from SAS) */
options noxsync noxwait noxmin;
filename excelchk dde 'excel|system' command;
%let excelstarted = %sysfunc(fopen(excelchk,S));
data _null_;
length fid rc start stop time 8;
fid=&excelstarted;
if fid le 0 then do;
rc=system('start excel');
start=datetime();
stop=start+10;
do while (fid le 0);
fid=fopen('excelchk','s');
time=datetime();
if (time ge stop) then fid=1;
end;
end;
run;
%let rc = %sysfunc(fclose(&excelstarted));
filename excelchk clear;
/* Connect to Excel */
filename cmds dde 'excel|system' command;
/* Open a specific file in excel */
data _null_;
file cmds;
/* set error handler off */
put '[Error(false)]';
Put "[open(""&logos\Logo Template.xls"",0,true)]";
put '[select("R1:R1")]'; /* select the rows containing the imagine */
Put '[COPY()]'; /* speaks for itself */
Put "[open(""&report\test.xls"",0,false)]"; /* open to overwrite Not READ ONLY*/
put '[workbook.activate("SUMMARY")]'; /* select tab name */
put '[select("R1:R1")]'; /* select place to past */
put '[INSERT(2)]'; /* inserts imagines above the selected */
Put '[COPY()]'; /* only required for other tabs */
/* loop all the remain tabs to add in the logos */
Do i=1 to &loop_num;
put '[ACTIVATE.NEXT("test.xls")]';
put '[select("R1:R1")]';
put '[INSERT(2)]';
Put '[COPY()]';
end;
/* select the summary sheet for saving purposes */
put '[workbook.activate("Summary")]';
put "[save.as(""&report\test.xls"",1)]"; /* 1 = save as normal excel */
put '[quit()]';
run;
I hope this helps.
Message was edited by: hallamus
... View more