BookmarkSubscribeRSS Feed

[엑셀] 읽기 암호가 부여된 엑셀 파일 읽기

Started ‎06-15-2020 by
Modified ‎06-15-2020 by
Views 133

* 읽기 암호가 부여된 엑셀 파일 읽기;

* Import password protected EXCEL into SAS;

 

* 출처 : http://sasstatistics.blogspot.kr/2017/06/import-password-protected-excel-into-sas.html;

 

%macro readpass(xlsfile1,xlsfile2,passwd,outfile,sheetname,getnames);

 

  options macrogen symbolgen mprint nocaps; options noxwait noxsync;

 

  /* %* we start excel here using this routine here   *; */

  /* 엑셀 공백 문서 열기 */

  filename cmds dde 'excel|system';

 

  data _null_;

       length fid rc start stop time 8;

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

       if (fid le 0) then do;

          rc=system('start excel');

          start=datetime();

          stop=start+20;

          do while (fid le 0);

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

             time=datetime();

             if (time ge stop) then fid=1;

          end;

       end;

       rc=fclose(fid);

  run; 

  quit;

 

  /* %* then we open the excel sheet here with its password *; */

  /* 읽기 암호가 부여된 엑셀파일 읽기 */

  filename cmds dde 'excel|system';

 

  data _null_;

      file cmds;

      put '[open("'"&xlsfile1"'",,,,"'"&passwd"'")]';

  run;

 

  /* %* then we save it without the password *; */

  /* 암호 없이 새로운 문서로 저장 */

  data _null_;

       file cmds;

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

       put '[save.as("'"&xlsfile2"'",51,"")]';

       put '[quit]';

  run;

 

  /* %* Then we import the file here *; */

  /* 새로운 엑셀파일 읽기 */

  proc import datafile="&xlsfile2" out=&outfile dbms=xlsx replace;

       %* sheet="%superq(datafilm&i)";

       sheet="&sheetname";

       getnames=&getnames;

  run; quit;

 

  /* %* then we destroy the non password excel file here *; */

  /* 신규로 생성한 엑셀 파일(암호가 제거된) 삭제 */

  systask command "del ""&xlsfile2"" ";

 

  proc contents data=&outfile varnum;

  run;

 

%mend readpass;

 

%readpass(d:\BACK pass test.xlsx, /* name of the xlsx 2007 file */

          d:
opass.xlsx,    /* temporary xls file for translation for import */

          test,              /* password of the excel spreadsheet          */

          work.back,        /* name of the sas dataset you want to write */

          sheet1,            /* name of the sheet */

          yes) ;             /* getnames  */

Version history
Last update:
‎06-15-2020 02:10 AM
Updated by:
Contributors

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Article Labels
Article Tags