Date:         Wed, 24 Aug 2011 03:30:31 -0400
Reply-To:     Søren Lassen <s.lassen@POST.TELE.DK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Søren Lassen <s.lassen@POST.TELE.DK>
Subject:      Re: Need Help with DDE to Excel: Highlighting Cells
Comments: To: Mary Rosenbloom <mary.rosenbloom.sas@GMAIL.COM>
Content-Type: text/plain; charset=ISO-8859-1

Mary,
You will have to send commands to Excel|system, e.g.:
filename cmds dde 'excel|system';

/* These PUT statements are       */
/* executing Excel macro commands */

data _null_;
   file cmds;
   put '[SELECT("R1C1:R20C3")]';
   put '[SORT(1,"R1C1",1)]';
   put '[SAVE()]';
   put '[QUIT()]';
run;

To find the right commands, you will have to get the manual for
Excel 4.0 macro functions (which is the format of the commands),
you can find that here:
http://support.microsoft.com/kb/q128185/

Regards,
Søren




On Tue, 23 Aug 2011 14:09:48 -0400, Mary Rosenbloom
<mary.rosenbloom.sas@GMAIL.COM> wrote:

>Hi all,
>
>I have been given an Excel template and need to send a listing of data to
>that exact template.  Ordinarily, I would use ODS to send data to Excel (I
>always use the ExcelXp tagset).  But, in this case I needed to use DDE.
>
>This was all going fine, until this week when they have now asked me to
>compare the previously sent listing (luckily I have saved the output
>dataset) to the current listing (also have this saved) and to highlight
>the cells where any of the variables have changed at all.  I will also be
>highlighting any new records that are in the current listing.
>
>My plan is to perform a merge between the OLD data and the NEW data each
>week, and to create some indicator variables (one for each column of
>interest) which will take the value of 0 if there is no change, 1 if there
>is a change.  Then I will use these indicator variables to determine if
>each cell should be shaded.
>
>The other thing that I have going for me is that I have named the
>variables that go into the listing according to the column that they go
>into, so they have names like: c1, c2, c3, ... c23.  I will be able to
>name the indicator variables similarily.
>
>Now to the question: can anyone help me with the code for highlighting
>cells in Excel using DDE?  I have used DDE with Word a lot, and I used to
>be able to run Word6 on a machine and use the macro editor within it to
>obtain the commands for DDE.  I *think* this can be done with Excel4, but
>I don't have access to that right now.  Can anyone point me in the right
>direction?
>
>Here is a sample of the main part of my code (after I open Excel, etc):
>
>***Create a filename for the data;
>filename lstvar dde "EXCEL|&thisfile!r10c1:r2000c100" notab;
>
>***read in the data;
>data _null_;
> set lstdata end=last;
> file lstvar lrecl = 32000;
> put c1   "09"x
>  c2  "09"x
>  c3   "09"x
>  c4   "09"x
>  c5  "09"x
>  c6   "09"x
>  c7   "09"x
>  c8  "09"x
>  c9   "09"x
>  c10   "09"x
>  c11  "09"x
>  c12   "09"x
>  c13   "09"x
>  c14  "09"x
>  c15   "09"x
>  c16   "09"x
>  c17  "09"x
>  c18   "09"x
>  c19   "09"x
>  c20  "09"x
>  c21   "09"x
>  c22   "09"x
>  c23  "09"x
>  c24  "09"x
> ;
> ***count the number of rows that were output;
> if last then do;
>  n = _n_;
>  call symput("Nrows",n);
> end;
>run;
>
>
>Thanks so much for your consideration.
>
>Mary Rosenbloom