Date: Wed, 24 Aug 2011 17:05:55 -0400
Reply-To: Nat Wooding <nathani@VERIZON.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Nat Wooding <nathani@VERIZON.NET>
Subject: Re: Need Help with DDE to Excel: Highlighting Cells
In-Reply-To: <CAHj_gPeZkOR0P64V5-Qc6OSMdAd+3sJYkOLOoquAJOD2dVGzDA@mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"
Mary
Well, at least I didn’t send a link to anything embarrassing. Let’s try this
again.
Nat
http://www.sas-consultant.com/professional/papers.html
_____
From: Mary Rosenbloom [mailto:mary.rosenbloom.sas@gmail.com]
Sent: Wednesday, August 24, 2011 4:12 PM
To: Nat Wooding
Subject: Re: Need Help with DDE to Excel: Highlighting Cells
Thanks Nat,
Did you mean to send me the Fibonacci link? I enjoyed it quite a bit, but
didn't see anything about DDE.
Cheers,
Mary R.
On Wed, Aug 24, 2011 at 12:11 PM, Nat Wooding <nathani@verizon.net> wrote:
Mary
In addition to the paper that you found, when you get a chance, look at some
that are cited in the following link.
Nat Wooding
http://britton.disted.camosun.bc.ca/fibslide/jbfibslide.htm
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Mary
Rosenbloom
Sent: Wednesday, August 24, 2011 12:34 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Need Help with DDE to Excel: Highlighting Cells
Hi Søren,
Thanks a lot for the reply. I struggled with this yesterday, and ended up
finding a nice reference, and even contacted the author, who sent a zip file
to me containing lots of other macros and programs:
http://www.lexjansen.com/mwsug/2010/excel_db/MWSUG-2010-166.pdf
However, due to the learning curve and the timeframe of the project, I
decided to work within the Excel template itself and set up conditional
formatting. So now I'll have some binary data that will be sent to a range
of columns far to the right of the file, then I'll either hide the columns
or change the font to white. These columns will be part of an Excel formula
that will be used to perform traffic lighting on other important cells in
the listing.
I was hoping that there was some way that I could use DDE to create a
traffic-lighted listing similar to how it is done in PROC REPORT, where the
listings and the traffic lighting are done together in the same proc step.
I am not able to find an example of that, though.
I'm not thrilled with this solution, and might revisit this issue again when
I have more time. I would really like to be able to do all of the work in
DDE/SAS rather than using Excel formulas.
Thanks for your help!
Cheers,
Mary R.
On Wed, Aug 24, 2011 at 12:30 AM, Søren Lassen
<s.lassen@post.tele.dk>wrote:
> 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
>
| 
