/* Richard A. DeVenezia * www.devenezia.com * * An example of using windows shell programming to manipulate Excel. * * Based on an April 5th, 2003 post to SAS-L * "Re: SAS to different worksheets in excel" * * The original post demonstrated how to use vba in excel to read different html files * into different tabs of an excel workbook. * * This example takes a different tack, a wsh (.vbs) script performs the * same actions as the vba program, but a vba program is not needed. the script * does everything and then goes away */ /* * Generate some html content */ /* * Suppress results viewer * (per SAS-L, David Ward Feb 21, 2002) */ filename oldprefs catalog 'work.tweak.oldprefs.source'; filename newprefs catalog 'work.tweak.newprefs.source'; proc registry export=oldprefs startat='ods\preferences\html'; run; data _null_; infile oldprefs; file newprefs; input; if scan(_infile_,1,'"')='AutoNavigate' then put '"AutoNavigate"=int:0'; else put _infile_; run; proc registry import=newprefs; run; /* * Fake data */ data foo; do sheet = 1 to 10; do row = 1 to 10; do class1 = 1 to 3; do class2 = 1 to 3; do i = 1 to 10 * ranuni(0) + 3; measure = ranuni(0); output; end; end; end; end; end; run; /* * Little macro to create sequence numbered html files containing * the output of a tabulate run */ %macro tabulate( data= , whereBy= , class= , var= , table= , path= , filenameAs= ); %local i fi file nWhereBy; proc sql noprint; select count(distinct &whereBy) into :nWhereBy from &data; %let nWhereBy=&nWhereBy; %do i = 1 %to &nWhereBy; %local whereBy&i; %end; select distinct sheet into :whereBy1-:whereBy&nWhereBy from &data; quit; %if &nWhereBy > 0 %then %do; %let fmt = z%eval (1 + %sysfunc(log10(&nWhereBy),12.)).; %do i = 1 %to &nWhereBy; %let fi = %sysfunc(putn(&i,&fmt)); %let file = %sysfunc(tranwrd(&filenameAs, :i:, &fi)); %* resolve template; ods html path=&path file="&file" style=sasweb; proc tabulate data=&data; where &whereBy=&&whereBy&i; class &class; var &var; table &table; run; ods html close; %end; %end; %else %put WARNING: &data had a problem with &whereBy..; %mend; /* * Invoke macro that generates content */ ods listing close; options nocenter nodate nonumber; title "Multi sheet import"; %let work = %sysfunc(pathname(WORK)); %tabulate ( data=foo , whereby=sheet , class=row class1 class2 , var=measure , table=%quote(row*measure*(mean N),class1*class2) , path="&work" , filenameAs=Sheet:i:.html %* templated filename; ) /* * Restore results viewer */ proc registry import=oldprefs; run; /* * write and execute a vbs script to read in N files from a folder, meeting some * filename specification */ %let vbs = &work.\loadsheets.vbs; filename vbs "&vbs"; data _null_; file vbs; input; put _infile_; cards4; path = WScript.Arguments(0) pattern = WScript.Arguments(1) outfile = WScript.Arguments(2) ' convert pattern (presumed to be DOSish) to regex pattern regex = Replace (pattern, ".", "\.") regex = Replace (regex, "*", ".*") regex = Replace (regex, "?", ".") regex = "^" & regex & "$" ' scan the files in the path, keeping track of which ones ' match the pattern in an array named source Set fs = CreateObject("Scripting.FileSystemObject") Set folder = fs.GetFolder (path) Set files = folder.Files Set rx = New RegExp rx.Pattern = regex rx.IgnoreCase = true Dim source() count = -1 For Each f in files if rx.Test (f.Name) then count = count + 1 ReDim preserve source(count) source(count) = f.Name end if Next if count = -1 then WScript.Quit ' might want to sort source(), but vbs has no sort function! ' so, forget about it ' open excel and make it work for us ' read in each source (html) into a new sheet, ' copy the whole thing and ' paste it into a new sheet in the workbook Set xl = CreateObject("Excel.Application") xl.Workbooks.Close Set book = xl.Workbooks.Add For i = lbound(source) to ubound(source) Set sheet = book.Sheets.Add(,book.Sheets(i+1)) sheet.Name = Left (source(i), Len(source(i))-5) Set parse = xl.Workbooks.Open (path &"\"& source(i)) parse.Sheets(1).UsedRange.Copy sheet.Paste xl.CutCopyMode = false ' Clear clipboard so big clipboard dialog will not appear when closing parse parse.Close sheet.Range("A1:A1").Select Next book.Sheets(1).Visible = false book.Sheets(1).Activate book.SaveAs (outfile) xl.Quit ;;;; run; filename vbs; options noxwait xsync xmin; x "start /wait &vbs ""&work"" ""sheet*.html"" ""c:\temp\tabulates.xls"""; /* * fire up excel again, and examine the results of running the vbs */ x "start c:\temp\tabulates.xls"; options xwait xsync;