Ever need to combine a bunch of separate Excel worksheets created with or without SAS into a single workbook?

Well then, have I got a Stupid SAS/Excel Trick for you!

Here's an example of how a SAS macro - %many2one - employs a technique that combines SAS, VBS, and VBA to automate the process of creating one Excel workbook from many individual Excel worksheets:

%many2one(in=c:\temp\classods.xls!classods
c:\temp\shoesods.xls!shoesods c:\temp\zipcodeods.xls!zipcodeods,
out=c:\temp\combined.xls);

Full code after the jump (indented code here) - report bugs in the comments!

• *-> Create A Few Worksheets;
• ods noresults;
• ods listing close;
• ods html body="c:\temp\classods.xls";
• proc print data=sashelp.class(obs=10);
• run;
• ods html close;
• ods html body="c:\temp\shoesods.xls";
• proc print data=sashelp.shoes(obs=10);
• run;
• ods html close;
• ods html body="c:\temp\zipcodeods.xls";
• proc print data=sashelp.zipcode(obs=10);
• run;
• ods html close;
• ods listing;
• ods results;
• *-> Macro to Combine Worksheets;
• %macro many2one(in=,out=);
• options noxwait;
• x erase "&out";
• options xwait;
• data _null_;
• file "c:\temp\class.vbs";
• put 'Set XL = CreateObject("Excel.Application")' /
• 'XL.Visible=True';
• %let n=1;
• %let from=%scan(&in,&n," ");
• %do %while("&from" ne "");
• %let fromwb=%scan(&from,1,"!");
• %let fromws=%scan(&from,2,"!");
• put "XL.Workbooks.Open ""&fromwb""";
• %if &n=1 %then
• put "XL.ActiveWorkbook.SaveAs ""&out"", -4143"%str(;);
• %else %do;
• put "XL.Workbooks(""%scan(&fromwb,-1,'\')"").Sheets(""&fromws"").Copy ,XL.Workbooks(""%scan(&out,-1,'\')"").Sheets(%eval(&n-1))";
• put "XL.Workbooks(""%scan(&fromwb,-1,'\')"").Close";
• %end;
• %let n=%eval(&n+1);
• %let from=%scan(&in,&n, " ");
• %end;
• put "XL.Workbooks(""%scan(&out,-1,'\')"").sheets(1).activate";
• put "XL.Workbooks(""%scan(&out,-1,'\')"").Save";
• put "XL.Quit";
• run;
• x 'c:\temp\class.vbs';
• %mend;
• *-> Example;
• %many2one(in=c:\temp\classods.xls!classods
• c:\temp\shoesods.xls!shoesods c:\temp\zipcodeods.xls!zipcodeods,
• out=c:\temp\combined.xls);