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"'")]';*/
