Hello All,
I'm running a code that will open an excel file and writes data into
it using DDE. I could open the file using X command, my excel has
multiple tabs. I want to save the excel file and close the entire
sheet once i'm done with updating the data. I found a code snippet on
web, but my SAS is throwing errors, the very last part where i'm
trying to close the file has an issue. I'm using 9.1.3  version. Any
ideas to crack this will be highly appreciated.

Here is my code:

options noxwait noxsync;
x '"c:\temp\SIU_FPA_CASES_BY_FCC.xls"';

data _null_;
 x=sleep(5);
run;

filename ddeopen1 DDE "Excel|system";

FILENAME OUT1_YR DDE "EXCEL|C:\temp\SIU_FPA_CASES_BY_FCC.xls\CW!
R22C1:R22C5";
FILENAME OUT2_YR DDE "EXCEL|C:\temp\SIU_FPA_CASES_BY_FCC.xls\FB!
R22C1:R22C5";
FILENAME OUT3_YR DDE "EXCEL|C:\temp\SIU_FPA_CASES_BY_FCC.xls\WB!
R22C1:R22C5";

%macro addtab (num,fcc);
data _null_;
 file out&num._yr;
 set siu_year_fcc;
   if fcc="&fcc";
   by fcc;
    put FPA_YTD_&yr. FPA_&prev_yr. new_cases_YTD_&yr.
new_cases_&prev_yr.;
run;
%mend addtab;

%addtab (1,00);
%addtab (2,05);
%addtab (3,04);

data _null_;
length cmnd $ 70;
file ddeopen1;
cmnd='[save("c:\temp\'||"%trim(SIU_FPA_CASES_BY_FCC)"||'.xls")]';
put cmnd;
put '[quit()]';
run;

ERROR in log:

NOTE: The file DDEOPEN1 is:
      DDE Session,
      SESSION=Excel|system,RECFM=V,LRECL=256

ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the data
step program.
       Aborted during the EXECUTION phase.

Thanks a lot in advance for looking into it.

Regards
Sri

Sri,

I definitely may have misunderstood your question.  From your code, it
appears that you have an Excel spreadsheet that already has the three
worksheets you are trying to populate.

If so, then the following modifications to your code may be all that
you need.  You didn't provide any sample data, thus I used
sashelp.class:

options noxwait noxsync;
x "c:\temp\SIU_FPA_CASES_BY_FCC.xls";

data _null_;
 x=3Dsleep(5);
run;

%let yr=3D2010;
%let prev_yr=3D2009;
data siu_year_fcc;
  set sashelp.class (rename=3D(
    sex=3DFPA_YTD_&yr. height=3DFPA_&prev_yr.
    weight=3Dnew_cases_YTD_&yr.
    name=3Dnew_cases_&prev_yr.));
    if _n_ eq 19 then _n_=3D0;
    fcc=3Dput(_n_,z2.);
run;

filename ddeopen1 DDE "Excel|system";

FILENAME OUT1_YR DDE "EXCEL|CW!R22C1:R22C5";
FILENAME OUT2_YR DDE "EXCEL|FB!R22C1:R22C5";
FILENAME OUT3_YR DDE "EXCEL|WB!R22C1:R22C5";

%macro addtab (num,fcc);
  data _null_;
    file out&num._yr;
    set siu_year_fcc;
    if fcc=3D"&fcc";
    put fcc FPA_YTD_&yr. FPA_&prev_yr. new_cases_YTD_&yr.
new_cases_&prev_yr.;
run;
%mend addtab;

%addtab (1,00);
%addtab (2,05);
%addtab (3,04);

data _null_;
  file ddeopen1;
  put '[SAVE()]';
  put '[QUIT()]';
run;

HTH,
Art
--------------
On Jul 6, 5:51=A0pm, Sri <subhadra...@gmail.com> wrote:
> Hello All,
> I'm running a code that will open an excel file and writes data into
> it using DDE. I could open the file using X command, my excel has
> multiple tabs. I want to save the excel file and close the entire
> sheet once i'm done with updating the data. I found a code snippet on
> web, but my SAS is throwing errors, the very last part where i'm
> trying to close the file has an issue. I'm using 9.1.3 =A0version. Any
> ideas to crack this will be highly appreciated.
>
> Here is my code:
>
> options noxwait noxsync;
> x '"c:\temp\SIU_FPA_CASES_BY_FCC.xls"';
>
> data _null_;
> =A0x=3Dsleep(5);
> run;
>
> filename ddeopen1 DDE "Excel|system";
>
> FILENAME OUT1_YR DDE "EXCEL|C:\temp\SIU_FPA_CASES_BY_FCC.xls\CW!
> R22C1:R22C5";
> FILENAME OUT2_YR DDE "EXCEL|C:\temp\SIU_FPA_CASES_BY_FCC.xls\FB!
> R22C1:R22C5";
> FILENAME OUT3_YR DDE "EXCEL|C:\temp\SIU_FPA_CASES_BY_FCC.xls\WB!
> R22C1:R22C5";
>
> %macro addtab (num,fcc);
> data _null_;
> =A0file out&num._yr;
> =A0set siu_year_fcc;
> =A0 =A0if fcc=3D"&fcc";
> =A0 =A0by fcc;
> =A0 =A0 put FPA_YTD_&yr. FPA_&prev_yr. new_cases_YTD_&yr.
> new_cases_&prev_yr.;
> run;
> %mend addtab;
>
> %addtab (1,00);
> %addtab (2,05);
> %addtab (3,04);
>
> data _null_;
> length cmnd $ 70;
> file ddeopen1;
> cmnd=3D'[save("c:\temp\'||"%trim(SIU_FPA_CASES_BY_FCC)"||'.xls")]';
> put cmnd;
> put '[quit()]';
> run;
>
> ERROR in log:
>
> NOTE: The file DDEOPEN1 is:
> =A0 =A0 =A0 DDE Session,
> =A0 =A0 =A0 SESSION=3DExcel|system,RECFM=3DV,LRECL=3D256
>
> ERROR: DDE session not ready.
> FATAL: Unrecoverable I/O error detected in the execution of the data
> step program.
> =A0 =A0 =A0 =A0Aborted during the EXECUTION phase.
>
> Thanks a lot in advance for looking into it.
>
> Regards
> Sri

On Jul 6, 11:03=A0pm, Arthur Tabachneck <art...@netscape.net> wrote:
> Sri,
>
> I definitely may have misunderstood your question. =A0From your code, it
> appears that you have an Excel spreadsheet that already has the three
> worksheets you are trying to populate.
>
> If so, then the following modifications to your code may be all that
> you need. =A0You didn't provide any sample data, thus I used
> sashelp.class:
>
> options noxwait noxsync;
> x "c:\temp\SIU_FPA_CASES_BY_FCC.xls";
>
> data _null_;
> =A0x=3Dsleep(5);
> run;
>
> %let yr=3D2010;
> %let prev_yr=3D2009;
> data siu_year_fcc;
> =A0 set sashelp.class (rename=3D(
> =A0 =A0 sex=3DFPA_YTD_&yr. height=3DFPA_&prev_yr.
> =A0 =A0 weight=3Dnew_cases_YTD_&yr.
> =A0 =A0 name=3Dnew_cases_&prev_yr.));
> =A0 =A0 if _n_ eq 19 then _n_=3D0;
> =A0 =A0 fcc=3Dput(_n_,z2.);
> run;
>
> filename ddeopen1 DDE "Excel|system";
>
> FILENAME OUT1_YR DDE "EXCEL|CW!R22C1:R22C5";
> FILENAME OUT2_YR DDE "EXCEL|FB!R22C1:R22C5";
> FILENAME OUT3_YR DDE "EXCEL|WB!R22C1:R22C5";
>
> %macro addtab (num,fcc);
> =A0 data _null_;
> =A0 =A0 file out&num._yr;
> =A0 =A0 set siu_year_fcc;
> =A0 =A0 if fcc=3D"&fcc";
> =A0 =A0 put fcc FPA_YTD_&yr. FPA_&prev_yr. new_cases_YTD_&yr.
> new_cases_&prev_yr.;
> run;
> %mend addtab;
>
> %addtab (1,00);
> %addtab (2,05);
> %addtab (3,04);
>
> data _null_;
> =A0 file ddeopen1;
> =A0 put '[SAVE()]';
> =A0 put '[QUIT()]';
> run;
>
> HTH,
> Art
> --------------
> On Jul 6, 5:51=A0pm, Sri <subhadra...@gmail.com> wrote:
>
>
>
> > Hello All,
> > I'm running a code that will open an excel file and writes data into
> > it using DDE. I could open the file using X command, my excel has
> > multiple tabs. I want to save the excel file and close the entire
> > sheet once i'm done with updating the data. I found a code snippet on
> > web, but my SAS is throwing errors, the very last part where i'm
> > trying to close the file has an issue. I'm using 9.1.3 =A0version. Any
> > ideas to crack this will be highly appreciated.
>
> > Here is my code:
>
> > options noxwait noxsync;
> > x '"c:\temp\SIU_FPA_CASES_BY_FCC.xls"';
>
> > data _null_;
> > =A0x=3Dsleep(5);
> > run;
>
> > filename ddeopen1 DDE "Excel|system";
>
> > FILENAME OUT1_YR DDE "EXCEL|C:\temp\SIU_FPA_CASES_BY_FCC.xls\CW!
> > R22C1:R22C5";
> > FILENAME OUT2_YR DDE "EXCEL|C:\temp\SIU_FPA_CASES_BY_FCC.xls\FB!
> > R22C1:R22C5";
> > FILENAME OUT3_YR DDE "EXCEL|C:\temp\SIU_FPA_CASES_BY_FCC.xls\WB!
> > R22C1:R22C5";
>
> > %macro addtab (num,fcc);
> > data _null_;
> > =A0file out&num._yr;
> > =A0set siu_year_fcc;
> > =A0 =A0if fcc=3D"&fcc";
> > =A0 =A0by fcc;
> > =A0 =A0 put FPA_YTD_&yr. FPA_&prev_yr. new_cases_YTD_&yr.
> > new_cases_&prev_yr.;
> > run;
> > %mend addtab;
>
> > %addtab (1,00);
> > %addtab (2,05);
> > %addtab (3,04);
>
> > data _null_;
> > length cmnd $ 70;
> > file ddeopen1;
> > cmnd=3D'[save("c:\temp\'||"%trim(SIU_FPA_CASES_BY_FCC)"||'.xls")]';
> > put cmnd;
> > put '[quit()]';
> > run;
>
> > ERROR in log:
>
> > NOTE: The file DDEOPEN1 is:
> > =A0 =A0 =A0 DDE Session,
> > =A0 =A0 =A0 SESSION=3DExcel|system,RECFM=3DV,LRECL=3D256
>
> > ERROR: DDE session not ready.
> > FATAL: Unrecoverable I/O error detected in the execution of the data
> > step program.
> > =A0 =A0 =A0 =A0Aborted during the EXECUTION phase.
>
> > Thanks a lot in advance for looking into it.
>
> > Regards
> > Sri- Hide quoted text -
>
> - Show quoted text -

Art,
Thank you very much for the response, this worked like a charm! Sorry
for not including my sample data, will definitely include it in my
further questions.

Best Regards
Sri