/*<pre><b>
/ Program      : xlsheets.sas
/ Version      : 2.1
/ Author       : Roland Rashleigh-Berry
/ Date         : 26-Jun-2011
/ Purpose      : Get a list of sheet names (topics) from an Excel spreadsheet
/                using DDE and write them to a global macro variable.
/ SubMacros    : none
/ Notes        : Sheet names containing spaces will be enclosed in double quotes
/                in the global macro variable. You must remove these double
/                quotes when using the %xl2sas macro as sheet names in quotes
/                are not accepted. Use the %dequote macro to do this. You can
/                extract each name in turn, whether quoted or not, using:
/                     %dequote(%scanq(&_xlsheets_,&i,%str( )))
/                and to count the number of sheet names to loop through use
/                %wordsq. Sheet names are returned in alphabetical order, rather
/                than sheet position order, due to the way "topics" are handled
/                by Excel.
/ Usage        : %xlsheets(C:\Mydata\Spread Sheet Name.xls);
/===============================================================================
/ PARAMETERS:
/-------name------- -------------------------description------------------------
/ xlfile            (pos) (no quotes) Full path name of spreadsheet file (allows
/                   spaces in the file name).
/ secswait=2        Number of seconds to wait for the spreadsheet to open
/ mvar=_xlsheets_   Name of global macro variable to receive sheet names
/===============================================================================
/ AMENDMENT HISTORY:
/ init --date-- mod-id ----------------------description------------------------
/ rrb  16Dec10         Use %sysexec instead of X command to open file to allow
/                      for spaces in the file name (v1.1)
/ rrb  20Dec10         Write sheet names to a global macro variable instead of
/                      to a dataset (v2.0)
/ rrb  08May11         Code tidy
/ rrb  26Jun11         Remove xlfile quotes if supplied (v2.1)
/===============================================================================
/ This is public domain software. No guarantee as to suitability or accuracy is
/ given or implied. User uses this code entirely at their own risk.
/=============================================================================*/

%put MACRO CALLED: xlsheets v2.1;

%macro xlsheets(xlfile,
              secswait=2,
                  mvar=_xlsheets_
                );

  %local errflag err savopts;
  %let err=ERR%str(OR);
  %let errflag=0;
  %if %length(&xlfile) %then %let xlfile=%sysfunc(dequote(&xlfile));

  %if not %length(&mvar) %then %let mvar=_xlsheets_;

  %global &mvar;
  %let &mvar=;


      /*----------------------------------*
              Check input parameters
       *----------------------------------*/


  %if not %length(&xlfile) %then %do;
    %let errflag=1;
    %put &err: (xlsheets) No Excel spreadsheet file name supplied to xlfile=;
  %end;
  %else %do;
    %if not %sysfunc(fileexist(&xlfile)) %then %do;
      %let errflag=1;
      %put &err: (xlsheets) xlfile=&xlfile can not be found;
    %end;
  %end;



  %if not %length(&secswait) %then %let secswait=2;
  %else %do;
    %if %length(%sysfunc(compress(&secswait,1234567890))) %then %do;
      %let errflag=1;
      %put &err: (xlsheets) An integer number of seconds is required. You specified secswait=&secswait;
    %end;
  %end;

  %if &errflag %then %goto exit;



      /*---------------------------------*
              Store current options
       *---------------------------------*/

  %*- store current xwait and xsync settings -;
  %let savopts=%sysfunc(getoption(xwait,keyword)) %sysfunc(getoption(xsync,keyword)); 



      /*---------------------------------*
              Read the spreadsheet
       *---------------------------------*/


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


  *- start up Excel by opening the spreadsheet -;
  %sysexec "&xlfile";


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


  *- assign filerefs -;
  filename _xlcmd dde 'Excel|system' lrecl=3000;
  filename _xltop dde 'Excel|system!topics' 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 topics -;
  data _null_;
    length topic scan2 $ 1000 storetop $ 30000;
    retain storetop " ";
    infile _xltop dlm='09'x dsd pad notab;
    input topic $ @@;
    if _n_>1 then do;
      if index(topic,'[')=1 then do;
        scan2=scan(topic,2,']');
        if length(scan2) > length(compress(scan2,' '))
          then storetop=trim(storetop)||' "'||trim(scan2)||'"';
        else storetop=trim(storetop)||' '||scan2;
        call symput("&mvar",trim(left(storetop)));
      end;
    end;
  run;


  *- close the spreadsheet and quit -;
  data _null_;
    file _xlcmd;
    put "[File.Close()]";
    put '[QUIT]';
  run;


  *- deassign filerefs -;
  filename _xltop clear;
  filename _xlcmd clear;


      /*---------------------------------*
                 Restore options
       *---------------------------------*/

  *- restore previous xwait and xsync settings -;
  options &savopts;




      /*---------------------------------*
                       Exit
       *---------------------------------*/

  %goto skip;
  %exit: %put &err: (xlsheets) Leaving macro due to problem(s) listed;
  %skip:

%mend xlsheets;