/*-------------------<-- Start of Description-->---------------------\
| The function is used to open an existing excel spreadsheet or open |
| an empty one;                                                      |
|---------------------<-- End of Description-->----------------------|
|--------------------------------------------------------------------|
|------------<-- Start of Files or Arguments Needed-->---------------|
| Argument:                                                          |
|   filepath:  the correct path and excel file name;                 |
|      if no argument is given or the filename is incorrect, then it |
|      opened a empty excel sheet;                                   |
|-------------<-- End of Files or Arguments Needed-->----------------|
|--------------------------------------------------------------------|
|------------------<-- Start of Files Created-->---------------------|
| Example:                                                           |
|    %excelopen(J:\CLINICAL\TACHY\Studydoc\Amazr\Stat\Table5A6A.xls);|
| Usage: %excelopen(filepath);                                       |
\-------------------<-- End of Files Created-->---------------------*/
%macro excelopen/parmbuff;
/*--------------------------------------------\
| Author:   Duo Zhou;                         |
| Created:  3-5-2001 9:23pm;                  |
| Modified: 1-15-2002 9:01pm;                 |
| Purpose:  Open an excel spread sheet;       |
\--------------------------------------------*/
%global excelfileref;
options noxwait noxsync;
x 'Exit';
%let num=1;
%let excelfileref=%qscan(&syspbuff,&num,%str(()""''));
%if (&excelfileref ne) and (%quote(%upcase(&excelfileref)) ne %quote(NEW)) %then %do;
   %if (%sysfunc(fileexist(&excelfileref))) %then %do;
      %put --> Note: Wait! The system is opening Excel Sheet: ;
      %put -->       "&excelfileref".;
      FILENAME _esys_ dde "excel|&excelfileref" command lrecl=1048576;
      data _null_;
         length fid rc 8;
         fid=fopen("_esys_", "s");
         if (fid le 0) then do;
            rc=system("'&excelfileref'");
            do until(fid);
               fid=fopen("_esys_", "s");
            end;
         end;
         rc=fclose(fid);
      run;
   %end;
   %else %do;
      %put ==> Alert! Can%str(%') find file "&excelfileref".;
   %end;
%end;
%else %do;
   %put --> Note: No excel sheet is provided, system is opening a new one for you.;
   Filename excelsys DDE "excel|system" lrecl=1048576;
   %let fid=%sysfunc(fopen(excelsys, s));
   data _null_;
   %if (&fid le 0) %then %do;
      rc=system("start excel"); x 'Exit';
      fid=fopen("excelsys", "s");
      do until(fid);
         fid=fopen("excelsys", "s"); 
      end;
      rc=fclose(fid);
   %end;
   %else %do; 
      %let rc=%sysfunc(fclose(&fid));
      file excelsys;
      put '[new(1)]';
   %end;
   run;
%end;
%mend excelopen;