Date:         Thu, 17 Dec 2009 07:48:53 -0600
Reply-To:     "Data _null_;" <iebupdte@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Data _null_;" <iebupdte@GMAIL.COM>
Subject:      Re: help needed on DDE 'excel|system'
Comments: To: Tom Quin <tomquin99@gmail.com>
In-Reply-To:  <1e68272c0912161736x4eeaa8bflc79204ff64b03d56@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

On 12/16/09, Tom Quin <tomquin99@gmail.com> wrote:
> Why excel|system can not function on my computer?

I don't know that it doesn't.  You have not shown enough code to
convince me that you have done all the steps required to send commands
to EXCEL using DDE.

Here is a complete program that you may be able to test.  It does....

1) Starts EXCEL
2) opens MYXLS.XLS workbook read only
3) determines the used range
4) clears formatting
5) reads 4 numeric fields
6) closes and quits, commented out for testing.

options noxwait noxsync;

data myxls;

   *** Open EXCEL using ROPER code;
   length fileref $8;
   rc  = filename(fileref,'EXCEL|SYSTEM','DDE');
   put rc= fileref=;

   fid = fopen(fileref,'S');          /* Check if Excel is open */
   if fid le 0 then do;               /* Excel is not open, open Excel
via Windows registry */
      rc=system("Start Excel");       /* DOS command to open Excel*/
      start = datetime();             /* Note start time */
      stop = start + 5;               /* Max time to try opening */
      do while (fid le 0);            /* Loop while Excel opens */
         fid = fopen(fileref,'S');    /* Check if Excel is open */
         time = datetime();           /* Reset current time */
         if time ge stop then fid = time; /* Set FID to terminate loop*/
         end;                         /* do while (fid le 0); */
      end;                            /* Excel is not open, open Excel
via Windows registry */
   rc = fclose(fid);                  /* Close fileopen on EXCEL */
   rc = filename(fileref);
   drop fileref rc fid start stop time;

   *** Open a workbook and read from it after clearing the formatting.;

   length ddetext $128;
   link dummy1;
   put '[OPEN(".\My SAS Files\9.1\Myxls.xls",0,"TRUE")]';
   put '[SELECT.SPECIAL(5)]';
   put '[CLEAR(2)]';

   ddeText = 'excel|system!Selection';
   infile dummy2 dde notab dlm='09'x dsd filevar=ddeText lrecl=256;
   input  selection:$128.;
   putlog selection;

   ddeText = cats('EXCEL|',selection);
   infile dummy3 dde notab dlm='09'x dsd end=eof filevar=ddeText
lrecl=256 missover;
   do while(not eof);
      input (f1-f4)(:f16.);
      output;
      end;
   link dummy1;
   *put '[CLOSE("FALSE","FALSE")]';
   *put '[QUIT()]';
   stop;
 dummy1:
   ddetext = 'excel|system';
   file dummy1 dde filevar=ddetext;
   return;
   run;
proc print;
   run;