Date:         Tue, 23 Aug 2011 14:09:48 -0400
Reply-To:     Mary Rosenbloom <mary.rosenbloom.sas@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary Rosenbloom <mary.rosenbloom.sas@GMAIL.COM>
Subject:      Need Help with DDE to Excel: Highlighting Cells

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