Date:         Mon, 11 Oct 1999 22:17:45 +0200
Reply-To:     "RLC Roger L. CALVEZ" <ROGER.CALVEZ@WANADOO.FR>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "RLC Roger L. CALVEZ" <ROGER.CALVEZ@WANADOO.FR>
Organization: Famille CALVEZ
Subject:      Re: Saving an Excel file in SAS with DDE ?

Hi,
this a sample by SAS

You can find it in SAS by the menu HELP/Sample programs
and search DDE in the index "onglet"


/****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: DDE3                                                */
 /*   TITLE: DDE Example 3 : Sending Commands to Excel via DDE   */
 /* PRODUCT: SAS                                                 */
 /*  SYSTEM: OS/2, Windows                                       */
 /*    KEYS: DDE DYNAMIC DATA EXCHANGE SYSTEM                    */
 /*   PROCS: PRINT                                               */
 /*    DATA:                                                     */
 /*                                                              */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*    DESC: Demonstrates how to use the SAS System to both      */
 /*          invoke Microsoft Excel, and issue commands via DDE  */
 /*          to control Excel.                                   */
 /*          Before submitting this code, change all references  */
 /*          to "x:\path\to\your\excel" to the path where your   */
 /*          installed copy of Microsoft Excel resides.          */
 /****************************************************************/

 OPTIONS NOXWAIT NOXSYNC;

 X "x:\path\to\your\excel\EXCEL.EXE";

FILENAME ddedata DDE "excel|sheet1!r1c1:r20c3";
DATA _NULL_;
     FILE ddedata;
     DO i = 1 TO 20;
         x = RANUNI(i);
         y = x + 10;
         z = x / 2;
         PUT x y z;
     END;
 RUN;


FILENAME ddecmds DDE "excel|system";
DATA _NULL_;
     FILE ddecmds;
     PUT '[select("r1c1:r20c3")]';
     PUT '[sort(1,"R1C1",1)]';
     PUT '[save.as("dde3.xls")]';
     PUT '[quit()]';
 RUN;


Another possibility to find syntax for a specific command in Excel is by
recordin a macro which is execute all thing that you wanr and visualize the
code in the VBA windows.


Good work.
--
===============
RLC
Roger L. CALVEZ
FRANCE
=======================================
<Myra.Oltsik@RESPONSEINSURANCE.COM> a écrit dans le message :
OFC9FA97A6.2079576B-ON85256807.00591F57@responseinsurance.com...
> I have a program that opens Excel and a specific spreadsheet, then writes
> data to it using DDE. Now I want the SAS program to Save to a new
> spreadsheet and then close the spreadsheet.
>
> I open it with this simple code:
>
> +FILENAME CMDS DDE 'EXCEL|SYSTEM';
> +
> +DATA _NULL_;
> +   FILE CMDS;
> +   PUT '[OPEN("M:\MY DATA\CLAIMS\RESERVE STUDY TEMPLATE EFG.XLS")]';
> +RUN;
>
> I tried the following code to save and close the file, without knowing the
> right code. My intuition was wrong. I'd appreciate any help you have.
>
> +DATA _NULL_;
> +   FILE CMDS;
> +   PUT '[SAVEAS("N:\DATA\CLAIMS\Reserve Study &QUARTER Table
&STATE..xls")]';
> +   PUT '[CLOSE("N:\DATA\CLAIMS\Reserve Study &QUARTER Table
&STATE..xls")]';
> +RUN;
>
> Thank you.
>
> Myra