Date:         Fri, 28 Jul 2006 11:00:44 -0400
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: Help with DDE
Comments: To: Ran S <raan67@YAHOO.COM>

Ran,

I'd try replace the 3rd line of your code to specify the short forms of
the various directories.  Depending upon your actual directory structure,
the command will probably work better if it looks something like:

X "d:\Progra~1\Micros~1\OFFICE11\EXCEL.exe" ;

Of course, you'll have to find out if "Program Files" is
actually "Progra~1", and "Microsoft Office" is actually "Micros~1" on your
machine.

HTH,
Art
-----------
On Fri, 28 Jul 2006 09:36:38 -0400, Ran S <raan67@YAHOO.COM> wrote:

>Hi, I need help with DDE funtion. When I ruunning my sas code I am getting
>this error:
>
>ERROR: Physical file does not exist, Excel|[BOOK1]Sheet2!r1c1:r185c6.
>NOTE: The SAS System stopped processing this step because of errors.
>NOTE: DATA statement used:
>      real time           0.07 seconds
>      cpu time            0.00 seconds
>
>SAS code is shown below:
>I would appreciate your help. Thanks! Ran
>
>
>
>options noxwait noxsync ;
>
>*INVOKE MICROSOFT EXCEL;
>X "C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE11\EXCEL.exe" ;
>
>* Microsoft Excel defines the DDE topic "SYSTEM" to;
>* allow applications to send commands in order to perform ;
>* specific Excel tasks.  This next FILENAME statement defines ;
>* a fileref to the Excel SYSTEM topic --ADDED BY AT;
>
>filename cmds DDE "excel|system" ;
>
>*WAIT FOR FEW SECONDS TO ALLOW EXCEL TO COME UP;
>data _null_ ; rc = sleep (5) ;  run ;
>
>/*======================================================================
>                     PART 1
>=======================================================================*/
>options mlogic symbolgen mprint ;
>
>%Macro P1(DS, SHEET) ;
>
>%let DSET = &DS ;
>             %if %sysfunc(index(&DSET,.)) > 0 %then
>                %do;
>                  %let MEM = %scan(&DSET, 2, .);
>                  %let L   = %scan(&DSET, 1, .);
>                %end;
>                %else %do;
>                  %let MEM = &DSET ;
>                  %let L   = WORK ;
>                %end;
>           proc sql noprint;
>             select nobs into :NUMBEROFOBS
>             from sashelp.vtable
>             where libname = "&L" and memname = "&MEM"
>             ;
>           quit;
>
>       %if %eval(&NUMBEROFOBS) ^= 0 %then
>      %do ;
>          filename XL1 dde "Excel|[BOOK1]Sheet&SHEET.!r1c1:r185c6" ;
>data _null_;
>  length tab $ 1 ;
>    set &DS ;
>      file XL1 notab ls = 32767 ;
>        tab  = "09"x ;
>        if _N_ = 1  then
>           put
>               "ID"        tab
>               "UID"       tab
>               "SIGSTATE"  tab
>               "ORGNAME_D" tab
>               "ORGNAME_G" tab
>               "ORGNAME_J" //
>                ;
>           put  ID_ tab UID tab SIGSTATE tab
>                ORGNAME_D tab ORGNAME_G ORGNAME_J;
>run ;      %end ;
>      %if %eval(&NUMBEROFOBS) = 0 %then
>      %do ;
>
>          filename XL1 dde "Excel|[BOOK1]Sheet&SHEET.!r1c1:r185c6" ;
>data _null_;
>   length tab $ 1 ;
>      file XL1 notab ;
>        tab  = "09"x ;
>                if _N_ = 1  then
>                    put
>                     /////  tab tab tab "INTERVENTIONS NOT FOUND." ;
>run ;
>    %end ;
>%Mend P1 ;
>                    %P1(P1MATCH,  1)
>                    %P1(P1NMATCH, 2) ;