Date: Wed, 9 Jun 1999 09:17:13 -0400
Reply-To: "Brucken, Nancy" <Nancy.Brucken@WL.COM>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: "Brucken, Nancy" <Nancy.Brucken@WL.COM>
Subject: Re: dde to excel
Content-Type: text/plain; charset="iso-8859-1"
Hi Kunling,
You can paste directly over the old worksheet with DDE, and your new
information will overwrite the old data. However, if you'd like to make
sure that everything gets cleared out, you can write a series of Excel
macros to delete cell contents, formats, etc. that you would run before
updating the worksheet, and then just call those from SAS, as well. I don't
know Excel that well, but I think there's also a place to store macros in
Excel so that they are executed automatically when a worksheet is opened-
that might be another approach. We've taken the first path; that is, our
programs call several Excel macros to completely clear out everything that
we don't want hanging around in a worksheet before dumping in the new data.
For example, the following VBA macro erases all cells from your starting
point to the end of the table:
Sub ClearContents()
'
' ClearContents Macro
' Macro created 4/2/98 by Nancy Brucken
' to clear contents of all cells in the
' worksheet.
Range(ActiveCell, Selection.SpecialCells(xlCellTypeLastCell)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("A1").Select
End Sub
The SAS macro that we've written to call it looks like:
%macro clearold (cleartyp,
StRow=%unquote(&StartRow),StCol=%unquote(&StartCol));
********************************************************;
* This macro clears all cell contents and borders, as *;
* specified in the call. *;
* *;
* Original version: N. Brucken 04/03/98 *;
* *;
* Macro variables used: *;
* CLEARTYP = CONTENTS/BORDERS/COLORS *;
* STCOL = Starting column of cells to be cleared*;
* STROW = Starting row of cells to be cleared. *;
* *;
********************************************************;
%local StRow StCol;
data _null_;
file cmds;
put %unquote(%str(%'[SELECT("R&StRow.C&StCol")]%'));
put %unquote(%str(%'[RUN("Personal.xls!Clear&cleartyp")]%'));
run;
%mend clearold;
Hope this helps,
Nancy
-----Original Message-----
From: Kunling Lu [mailto:Kunling.Lu@kp.org]
Sent: Tuesday, June 08, 1999 6:56 PM
To: Nancy.Brucken
Cc: SAS-L; SAS-L
Subject: RE: dde to excel
Nancy, I submitted the sas code to invoke and run your vbScript. The
problem is how to tell the EXCEL to overwrite the old file (sheet1.xls).
The desired sequence is: (1) open the template (sheet1.xls) with the macro,
(2) dde update the template, (3) save it and store it with another name in
version 5/95. This loop will be in a macro so that data can be populated.
I stumbled at what code in SAS to control this EXCEL overwrite behavior. I
cc this MSG to the SAS-L group in case someone out there already dealt with
it before. I also bcc to Matt and Karl who had an excellent paper for WUSS
98.
Your help is precious. -Kunling
| 
