Date:         Thu, 20 Mar 2008 07:11:30 -0700
Reply-To:     RolandRB <rolandberry@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         RolandRB <rolandberry@HOTMAIL.COM>
Organization: http://groups.google.com
Subject:      sas calling Excel which is translating "1-5" to "01-May"
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

I am reading in an html file into Excel using sas code. The html file
looks fine but when Excel reads it in it converts text number ranges
such that "1-5" is shown as "01-May" and the following "6-20" is shown
as "Jun-20". How do I issue the commands to Excel from my SAS code so
that it does not do this. How do I amand the code I am using below?


*- set required options for dde to work correctly -;
options noxwait noxsync;


*- start up Excel by opening the spreadsheet -;
X &xlfile;


*- wait for Excel to finish starting up -;
data _null_;
  x=sleep(&secswait);
run;


*- assign filerefs -;
filename _xlin dde "Excel|&sheetname!
R&startrow.C&startcol:R&endrow.C&endcol" lrecl=3000;
filename _xlcmd dde 'Excel|system' lrecl=3000;


*- Excel command to remove new-line characters -;
data _null_;
  file _xlcmd;
  put "[error(FALSE)]";
  put "[FORMULA.REPLACE(""%sysfunc(byte(10))"","""",
2,1,FALSE,FALSE)]" ;
run;


*- read in the spreadsheet page -;
data &dsout;
  length &vpref.1-&vpref.&maxvarnum $ &vlen;
  infile _xlin dlm='09'x notab dsd pad missover;
  input &vpref.1-&vpref.&maxvarnum;
run;