DDE to Excel - Nothing Gets Written...

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

DDE to Excel - Nothing Gets Written...

Hello, All!

I've been working on setting up an Excel template that I can write to with DDE (unfortunately, my company does not have SAS/Access for PC files  :-(  ).  I can get DDE to open the template, save it to another name in another directory and activate the sheet I want to write to.  But when it comes time to write out the data, nothing happens.  No error messages in the log.  Everything looks like it worked.  I'm stumped.

I looked through prior posts and I think my triplet is correctly coded.  Any help would be greatly appreciated!

Here is the log.  Template, code and input file are attached.  I had to save the template as xls b/c xlsx docs are not allowed.  I am using SAS 9.3 and XL 2010.

 

2497 /*********************************************************************************/

2498 /* Starting Options                                                              */

2499 /*********************************************************************************/

2500

2501 options macrogen symbolgen mprint nocaps noxwait noxsync;

2502

2503 /*********************************************************************************/

2504 /* Filename through which we pipe DDE commands to XL                            */

2505 /*********************************************************************************/

2506

2507 filename cmds dde 'excel|system';

2508 /*********************************************************************************/

2509 /* Start XL                                                                      */

2510 /*********************************************************************************/

2511

2512 data _null_;

2513 length fid rc start stop time 8;

2514

2515 fid=fopen('cmds','s');

2516 if (fid le 0) then do;

2517 rc=system('start excel');

2518 start=datetime();

2519 stop=start+5;

2520 do while (fid le 0);

2521 fid=fopen('sas2xl','s');

2522 time=datetime();

2523 if (time ge stop) then fid=1;

2524 end;

2525 end;

2526 rc=fclose(fid);

2527

2528 run;

NOTE: DATA statement used (Total process time):

real time 5.04 seconds

cpu time 5.09 seconds

 

2529 /*********************************************************************************/

2530 /* Open the XL template                                                          */

2531 /*********************************************************************************/

2532

2533 data _null_;

2534 file cmds;

2535

2536 put '[open("D:\Templates\TEMPLATE_Test.xlsx")]';

2537

2538 run;

NOTE: The file CMDS is:

DDE Session,

SESSION=excel|system,RECFM=V,LRECL=256

NOTE: 1 record was written to the file CMDS.

The minimum record length was 41.

The maximum record length was 41.

NOTE: DATA statement used (Total process time):

real time 0.37 seconds

cpu time 0.04 seconds

 

2539 /*********************************************************************************/

2540 /* Save the XL file to the Report File Name                                      */

2541 /*********************************************************************************/

2542

2543 data _null_;

2544 file cmds;

2545

2546 put '[error("false")]';

2547 put '[save.as("D:\Output\TestReport.xlsx")]';

2548

2549 run;

NOTE: The file CMDS is:

DDE Session,

SESSION=excel|system,RECFM=V,LRECL=256

NOTE: 2 records were written to the file CMDS.

The minimum record length was 16.

The maximum record length was 38.

NOTE: DATA statement used (Total process time):

real time 0.17 seconds

cpu time 0.04 seconds

 

2550

2551 filename cmds dde 'excel|system';

2552 /*********************************************************************************/

2553 /* Activate the sheet we want                                                    */

2554 /*********************************************************************************/

2555

2556 data _null_;

2557 file cmds;

2558

2559 ddecmd = '[workbook.activate("Summary Reports",false)]';

2560 put ddecmd;

2561

2562 run;

NOTE: The file CMDS is:

DDE Session,

SESSION=excel|system,RECFM=V,LRECL=256

NOTE: 1 record was written to the file CMDS.

The minimum record length was 44.

The maximum record length was 44.

NOTE: DATA statement used (Total process time):

real time 0.06 seconds

cpu time 0.04 seconds

 

2563 /*********************************************************************************/

2564 /* File Name to pass data through DDE                                            */

2565 /*********************************************************************************/

2566

2567 filename xlout dde

2568 "excel|Summary Reports!R11CG:R19CI";

2569

2570 data _null_;

2571 file xlout;

2572 set lps;

2573

2574 put col1 col2 col3;

2575

2576 run;

NOTE: The file XLOUT is:

DDE Session,

SESSION=excel|Summary Reports!R11CG:R19CI,

RECFM=V,LRECL=256

NOTE: 9 records were written to the file XLOUT.

The minimum record length was 11.

The maximum record length was 29.

NOTE: There were 9 observations read from the data set WORK.LPS.

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.03 seconds

Attachment
Attachment

Accepted Solutions
Solution
‎08-16-2013 03:39 PM
Super Contributor
Posts: 333

Re: DDE to Excel - Nothing Gets Written...

I believe the syntax for the cell range has to be R1C1 format. Do you try changing referring to column G as 7 and column I as 9?

EJ

View solution in original post


All Replies
Solution
‎08-16-2013 03:39 PM
Super Contributor
Posts: 333

Re: DDE to Excel - Nothing Gets Written...

I believe the syntax for the cell range has to be R1C1 format. Do you try changing referring to column G as 7 and column I as 9?

EJ

Contributor
Posts: 27

Re: DDE to Excel - Nothing Gets Written...

Thank you, esjackso1!  I've been going nuts for two days, now.  :-)


Super Contributor
Posts: 333

Re: DDE to Excel - Nothing Gets Written...

No problem ... glad it helped!

EJ

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 336 views
  • 2 likes
  • 2 in conversation