Import password protected EXCEL into SAS

Click Here

%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(j:\access\accpcff\excelfiles\passpro.xlsx, /* name of the xlsx 2007 file */
          c:\sastest\nopass.xlsx,  /* temporary xls file for translation for import */
    mypass,               /* password of the excel spreadsheet          */
           work.temp1,  /* name of the sas dataset you want to write */
           sheet1,     /* name of the sheet */
           yes) ;     /* getnames  */