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