Date:         Fri, 24 Jan 2003 10:20:03 -0500
Reply-To:     "Brucken, Nancy" <Nancy.Brucken@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Brucken, Nancy" <Nancy.Brucken@PFIZER.COM>
Subject:      Re: Macro resolution of Excel commands via DDE
Comments: To: Kevin Viel <kviel@EMORY.EDU>
Content-Type: text/plain; charset="iso-8859-1"

Hi Kevin,
  Try adding a %unquote to the string, and you also don't need some of the
extra %'s in there (at least, our stuff doesn't have them):

put %unquote(%str(%'[select("r&start.c2:r&end.c6")]%'));

Hope this helps,
  Nancy

Nancy Brucken
Development Informatics
Pfizer Global Research & Development, Ann Arbor
(734) 622-5767
E-mail address:  Nancy.Brucken@pfizer.com


-----Original Message-----
From: Kevin Viel [mailto:kviel@EMORY.EDU]
Sent: Friday, January 24, 2003 10:07 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Macro resolution of Excel commands via DDE


Greetings,

                I am attempting to write a macro that sends formatting
functions to Excel via DDE.

    I have the following line:

put %str(%'[select%(%")r&start.c2:r&end.c6 %str(%"%)]%');

    which should resolve to:

'[select("r6c2:r30c6")]'

I obtained the above line by using %PUT instead of PUT.  When I use PUT I
get the following error:

MPRINT(SECTION_FORMAT):   put '[workbook.activate("sheet1")]';
NOTE: Line generated by the invoked macro "SECTION_FORMAT".
5           put '[select("r&start.c2:r&end.c6 ")]'; *     put
'[select("r1c2:r1c6 ")]';
                -                                -
                22                               76
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted
string,
              arrayname, #, (, +, /, //, ;, @, @@, OVERPRINT, _ALL_,
_BLANKPAGE_, _ODS_,
              _PAGE_.

ERROR 76-322: Syntax error, statement will be ignored.

                  -
                  200
ERROR 200-322: The symbol is not recognized and will be ignored.

    Please not that on line 5 above, the commented section resolves.  What
am I overlooking?  The entire macro follows my signature.


Thank you,

Kevin

____________________________________

Kevin Viel
Department of Epidemiology
Rollins School of Public Health
Emory University
Atlanta, GA 30329


%macro section_format;
  /* Determine how many cells per Project / SNP */
  data _null_;
    cnt+1;
    do i=1 by 1 until (last.sort);
      set all_review end=end;
      by proj sort;
    end;
    call symput("Sec" || put(cnt,3. -L), put(i-1, 8.));
    if end then call symput("Sections", put(cnt, 8.));
  run;

  %let start=6;
  %let end=0;

  data _null_;
  file xl_sys;
  put '[error(false)]';
  put '[workbook.activate("sheet1")]';
   %do i=1 %to &sections.;
      %let end=%eval(&start.+&&&sec&i.-1);
   put %str(%'[select%(%")r&start.c2:r&end.c6 %str(%"%)]%');
*   put %str(%'[select%(%")r1c2:r1c6 %str(%"%)]%');
      put '[format.font("Arial", 10, true, false, false, false, 0, false,
false)]';
      put '[alignment(3)]';
      put '[format.number("@")]';
   %let start=%eval(&end.+7);
   %end;
  run;

  %global all_end;
  %let all_end=&end.;
%mend section_format;


LEGAL NOTICE
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.