Date: Mon, 11 Mar 2002 20:59:09 +0000
Reply-To: Prasad Prabhudesai <prasad_prabhud@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Prasad Prabhudesai <prasad_prabhud@HOTMAIL.COM>
Organization: Mailgate.ORG Server - http://www.Mailgate.ORG
Subject: Re: SAS to EXCEL DDE question
Content-Type: text/plain; charset=ISO-8859-1
Thanks a lot Nancy Brucken for the following macro but somehow nothing
gets
resolved in the macro.
I am using the following macro to populate the
certain cells in EXCEL but somehow nothing gets resolved and I get
DDE error. I would appreciate the if anybody can help me in this.
%MACRO DUMPDATA(start=,end=);
293
294 %do I = &start %to &end;
295 PUT '[SELECT("R2C&I")]';
296 %DO J = %eval(&end - &start) %to 1;
297 PUT '[FORMULA(" ' DX&J ' ")]';
298 %END;
299 %END;
300
301 %MEND DUMPDATA;
302
303
304
305
306 OPTIONS NOXWAIT NOXSYNC MACROGEN SYMBOLGEN;
307
308 FILENAME CMDS DDE 'EXCEL|SYSTEM';
309
310 DATA _NULL_;
311 SET LAST;
312 FILE CMDS;
313 PUT '[WORKBOOK.SELECT("tier")]';
314 /*** NRXs Below *************/
315 %dumpdata(start=3,end=23);
SYMBOLGEN: Macro variable START resolves to 3
SYMBOLGEN: Macro variable END resolves to 23
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
MACROGEN(DUMPDATA): PUT '[SELECT("R2C&I")]';
SYMBOLGEN: Macro variable END resolves to 23
SYMBOLGEN: Macro variable START resolves to 3
316 run;
NOTE: The file CMDS is:
DDE Session,
SESSION=EXCEL|SYSTEM,RECFM=V,LRECL=256
ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the data
step program.
Aborted during the EXECUTION phase.
NOTE: 1 record was written to the file CMDS.
The minimum record length was 25.
The maximum record length was 25.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.LAST.
NOTE: DATA statement used:
real time 6:07.94
cpu time 0.08 seconds
"Prasad Desai" <prasad@prabhudesai.com> wrote in message
news:8ca3a7824bd09a32b96cfb66e9dbefdc.3279@mygate.mailgate.org...
> I am trying to populate certain cells in the EXCEL.
> It works alright but I want to make it more efficient by using do loop.
>
> Following is the part of my code.
>
>
>
> OPTIONS NOXWAIT NOXSYNC;
> FILENAME CMDS DDE 'EXCEL|SYSTEM';
>
> DATA _NULL_;
> FILE CMDS;
> PUT '[OPEN("H:\TEST.XLS")]';
> RUN;
>
> DATA _NULL_;
> SET LAST;
> FILE CMDS;
> PUT '[WORKBOOK.SELECT("main")]';
>
> PUT '[SELECT("R2C3")]';
> PUT '[FORMULA(" ' dx2 ' ")]';
>
> PUT '[SELECT("R2C4")]';
> PUT '[FORMULA(" ' dx1 ' ")]';
> RUN;
>
> This program runs weekly so the FORMULA part and SELECT part needs to be
> changed. I want to put this in a do loop so that I don't have to change
> this part manually. This will run for 20 weeks.
>
> so I want the code something like
>
> DATA _null_;
> SET LAST;
> FILE CMDS;
> PUT '[WORKBOOK.SELECT("main")]';
>
> DO I = 3 TO 23;
> PUT '[SELECT("R2C.&I")]'; /** Row will not change, only Column will c
> hange . Column starts from 3**/
> DO J= 20 to 1 ;
> PUT '[FORMULA(" ' dx.&J ' ")]';
>
> END;
> END;
> RUN;
>
>
>
> Can somebody help me in this.
>
> Thanks
>
> Prasad
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
