Help using Base SAS procedures

Import a password protected Excel doc

Reply
Regular Contributor
Posts: 173

Import a password protected Excel doc

Hello,

How do I import a password protected Excel document? My proc imports works fine any non-password protected excel doc. I tried adding 'password = ' to the code below, but got an error with the password= option. Any input will be greatly appreciated.

Thanks!

proc import out= ssn1
datafile= "&Ssn_FILE"
dbms=EXCEL replace;
mixed = yes;
sheet= "Sheet1";
run;
Valued Guide
Posts: 634

Re: Import a password protected Excel doc

Shows what I know. I did not even know that an Excel file could be password protected.

Have you tried passing the password through the PIPE engine on the FILENAME statement? That is just a guess.
Super User
Posts: 10,046

Re: Import a password protected Excel doc

Hi .
Maybe you need build a ODBC connection for that xls file,because SAS treat Excel file as a library,but it is too troubled.Or recommend you to use password to open that Excel file and File-> Save as->xls file to make a xls file without password.


Ksharp
Regular Contributor
Posts: 151

Re: Import a password protected Excel doc

Regular Contributor
Posts: 151

Re: Import a password protected Excel doc

I'm not an expert in VBA but you can use MS Excel programming language (VBA) and PIPE engine to disable the password protection.
The code below sets the password. I'm not the author of this code. I've find it somewhere and It was very helpful as a solution of some tasks in different modifications. For example, I've modified it to rename Excel sheets.
You can change the VBA code to disable password protection.

[pre]
filename IN '.\book1.xls';
filename OUT '.\book1PW.xls';
%let PW = PWvalue;
data _null_;
length script filevar command workbook1 workbook2 $256 pw $16;
script = catx('\',pathname('WORK'),'PASSWORD.vbs');
filevar = script; workbook1 = pathname('in');
workbook2 = pathname('out');
pw = symget('PW');
file dummy1 filevar=filevar;
put 'Const ' workbook1=$quote256.;
put 'Const ' workbook2=$quote256.;
put 'Const ' pw=$quote18.;
put 'Set objExcel = CreateObject("Excel.Application")';
put 'objExcel.Visible = FALSE';
put 'objExcel.DisplayAlerts = FALSE';
put 'Set objWorkbook = objExcel.Workbooks.Open(workbook1)';
put 'objWorkbook.SaveAs workbook2,,pw';
put 'objExcel.Quit';
/* close the script file by opening another file */
filevar = catx('\',pathname('WORK'),'DUMMY2.vbs');
file dummy1 filevar=filevar;
/* look at the script, not necessary but may be useful */
infile dummy3 filevar=script end=eof;
do _n_ = 1 by 1 until(eof); input;
putlog 'NOTE: ' _n_ 3. +1 _infile_; end;
/* call the script */
command = catx(' ','cscript',quote(strip(script)));
infile dummy4 pipe filevar=command end=eof;
do until(eof);
input ;
putlog _infile_;
end;
stop;
run;

[/pre]
Regular Contributor
Posts: 173

Re: Import a password protected Excel doc

Thank you all very much for your help! I copied the content of the excel to another excel without password proection then read from the unprotected file. I will try your methods/code above.

Thank you agian!
Ask a Question
Discussion stats
  • 5 replies
  • 11156 views
  • 2 likes
  • 4 in conversation