I was browsing the scripting guys archive and noticed ScreenUpdateing "method?". The excel part can be sped up about 2x by using

objExcel.ScreenUpdating = False

Then turning it back on TRUE when you want to see the results.

Or you could run it in "silent mode" with visible=FALSE for the entire process.

On 11/4/09, Chang Chung <chang_y_chung@hotmail.com> wrote: > On Mon, 2 Nov 2009 11:58:33 -0600, Data _null_; <iebupdte@GMAIL.COM> wrote: > ... > >I don't now if this will be acceptable, but it does operate without > >SAS/ACCESS to PC file formats. It does require EXCEL. > ... > Hi, _null_, > > Thanks for sharing a piece of excellent code. I would add that in order to > run, it also requires Windows Script Host enabled. > > In terms of coding styles, it can be made clearer by separating (as much as > we can) the window scripting vbs code from the sas data step code. Here is > my try using a technique of expanding placeholders. I also love to clean up > as much as I can. This ran on sas 9.2 on windows vista with excel 2007 > installed locally. > > Cheers, > Chang > > > /* exporting dataset to an excel sheet > via WScript (vbs). Slow but flexible. > Modified from data _null_^s SAS-L > posting archived at UGA: http://tinyurl.com/yl9bvxb or > http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0911a&L=sas-l&O=A&P=13475 > chang_y_chung@hotmail.com 20081104 > */ > > %let data = sashelp.shoes; > %let pwd = %sysfunc(pathname(WORK)); > %let vbs = &pwd\sas2exl.vbs; > %let xls = &pwd\sas2exl.xls; > > /* get the variable names and labels */ > proc contents data=&data out=contents(keep=name label > rename=(name=_name label=_label)) noprint; > run; > > /* write a WSH script in vbs. > substituting placeholders with data, if any */ > data _null_; > infile cards; > file "&vbs"; > length _inLine _outLine _value $32767; > input; > _inLine = _infile_; > > /* line without place holders */ > if index(_inLine, "#row#") = 0 then do; > _outLine = _inLine; > link putLine; > return; > end; > > /* header row */ > do _col = 1 by 1 while (not _headerDone); > set contents end = _headerDone; > _row = 1; > _value = quote(trim(_name)); > if not missing(_label) then do; > _newLine = " & vbLF & vbLF & "; > _label = quote(trim(_label)); > _value = catx(_newLine, _value, _label); > end; > _valueQuote = 0; > link expand; > link putLine; > end; > _nCols = _col - 1; /* we get to know the n columns */ > > /* other rows */ > do _row = 2 by 1 while (not _bodyDone); > set &data end = _bodyDone; > do _col = 1 to _nCols; > set contents point=_col; > _value = vvaluex(_name); > _valueQuote = 1; > link expand; > link putLine; > end; > end; > > return; /* end of main */ > > /* utilities */ > expand: > _value = trim(_value); > if _valueQuote then _value = quote(trim(_value)); > _outLine = trim(tranwrd(tranwrd(tranwrd(_inLine, > "#row#", strip(put(_row, best.))), > "#col#", strip(put(_col, best.))), > "#value#", _value) > ); > return; > > putLine: > _outLine = trim(_outLine); > _lineLength = length(_outLine); > put _outLine $varying. _lineLength; > return; > > /* vbs file template */ > cards; > ' sas2excel.vbs -- automatically generated > > ' open a sheet > Set app = CreateObject("Excel.Application") > app.Visible = True > app.DisplayAlerts = False > app.Workbooks.Add > > ' write data out > Set sheet = app.ActiveWorkbook.Worksheets(1) > sheet.Cells(#row#, #col#).Value = #value# > > ' a little bit of formatting > sheet.UsedRange.columns.autofit > > ' save the excel sheet and clean up > app.DisplayAlerts = False ' to overwrite > app.ActiveWorkbook.SaveAs(WScript.Arguments(0)) > app.Quit > set sheet = nothing > set app = nothing > ; > run; > > /* call the window script -- this should open excel */ > filename cscript pipe "cscript ""&vbs"" ""&xls"""; > data _null_; > infile cscript end=end; > do while(not end); > input; > put _infile_; > end; > stop; > run; > filename cscript clear; >