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
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
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;
set lstdata end=last;
file lstvar lrecl = 32000;
put c1 "09"x
***count the number of rows that were output;
if last then do;
n = _n_;
Thanks so much for your consideration.