How to use DDE to import SAS dataset to a protected sheet in Excel?

dliang — Thu, 06 May 2021 19:03:28 GMT

I have this macro here that import the SAS dataset into excel, but it only works for a non-protected sheet. Please see my 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;

I found a code to open a protected sheet with password. But I do not know where I should put it in this code. Can I get some advice?

/*put '[Open("'"&STUFILEin\ER_IP_TN_XX_YYYYMMDD_NEW_test.xlsm"'",,,,"'"centurion"'")]';*/

Re: How to use DDE to import SAS dataset to a protected sheet in Excel?

SASKiwi — Thu, 06 May 2021 22:23:45 GMT

Does this help https://support.sas.com/kb/31/328.html

topic Re: How to use DDE to import SAS dataset to a protected sheet in Excel? in SAS Programming

How to use DDE to import SAS dataset to a protected sheet in Excel?

Re: How to use DDE to import SAS dataset to a protected sheet in Excel?