How to fill excel formula in SAS DDE?

[dliang]

Hello,

I am using DDE to import excel files and overwriting them. I found that all excel formulas are gone after DDE re-write them. I see there is a way to put formula in excel by using DDE, something like this.

But I was using the DDE macros, I do not know where I should put this code. Can someone please give me some advices? See my DDE code below.

%let stufilein=U:\Reports and Data\TN Reporting;
%let stufileout=U:\Reports and Data\TN Reporting\TN_DL\Test;
%MACRO OPENxlsx(FOLDER=,IN=);

%LET FIL=;
DATA _null_;
LENGTH FILE $300.;
FILE="'&STUFILEin\&folder.&in.'";
FILE="'"!!TRIM(LEFT(TRANWRD(FILE,"'",'"')))!!"'";
CALL SYMPUT ("FIL",TRIM(LEFT(FILE)));
RUN;

options noxwait noxsync;
x &fil.;
data _null_;
filename commands dde "Excel|system";

/*run;*/
%MEND OPENxlsx;

/*********************************************************************************/
/* TO SAVE ACTIVE xlsx FILE UNDER SPECIFIC FOLDERS AND QUIT EXCEL */
/*********************************************************************************/
%MACRO CLOSExlsx(out=,quit=0);
%LET FIL=;
DATA _null_;
LENGTH FILE $300.;
FILE="[save.as('&STUFILEout.\&out.')]";
FILE="'"!!TRIM(LEFT(TRANWRD(FILE,"'",'"')))!!"'";
CALL SYMPUT ("FIL",TRIM(LEFT(FILE)));
RUN;
%PUT &FIL=;
data _null_;
file commands;
put &fil.;

put '[CLOSE()]';
%IF &quit=0 %then %do;
put '[QUIT()]';
stop;
run;
filename commands clear;
%END;
%IF &quit=1 %then %do;
run;
%END;
%MEND CLOSExlsx;

%OPENxlsx(FOLDER=,IN=ER_IP_TN_XX_YYYYMMDD_NEW_NEW.xlsm); /* Open the Excel template*/
option missing="" ;

/* First dataset is output in a range starting at row 4 column 6 and ending at row 4 column 6*/
filename TAB DDE "EXCEL|[ER_IP_TN_XX_YYYYMMDD_NEW_NEW.xlsm]TN ER & IP 24 Log!R4C1:R50C70";
filename xlcmds DDE "EXCEL|SYSTEM";
data _null_; /* Data step to output the data in the Excel file */
set nonarchive_&&initial&m.;
file TAB notab lrecl=7000;

PUT PATIENT_FIRST_NAME '09'x
PATIENT_LAST_NAME '09'x
DOB '09'x
TDOC_CHAR '09'x

;
run;

filename TAB DDE "EXCEL|[ER_IP_TN_XX_YYYYMMDD_NEW_NEW.xlsm]Archive!R4C1:R200C70";
filename xlcmds DDE "EXCEL|SYSTEM";
data _null_; /* Data step to output the data in the Excel file */
set archive_&&initial&m.;
file TAB notab lrecl=7000;

PUT PATIENT_FIRST_NAME '09'x
PATIENT_LAST_NAME '09'x
DOB '09'x
TDOC_CHAR '09'x
;
run;

%CLOSExlsx(OUT= &&source&m.);

/*%end;*/
%end;
%mend reports;
%reports;

[ballardw]

Since Microsoft is no longer supporting DDE and other software can interfere with the communications channels I would suggest using a different approach.

There is a SAS add in called Integration with Microsoft Office that might be a tad more robust going forward.

I am not sure that SAS can read the actual formula as the tools I've used want to get the value that would be resolved from that function in the spread sheet.

You can use TAGATTR style overrides in some procedures that will write functions into ODS EXCEL or ODS TAGSETS.EXCELXP output but not my personal strength.