The SAS Output Delivery System and reporting techniques

Set password for excel

Reply
N/A
Posts: 0

Set password for excel

Hi,
Is it possible to set a password for Excel book from within SAS (Base or EG)?
Respected Advisor
Posts: 3,799

Re: Set password for excel

Posted in reply to deleted_user
I don't know of a SAS PROC or function, but you can write a program, that writes a program that calls a program that sets the PW. Here in one example.

[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]
Ask a Question
Discussion stats
  • 1 reply
  • 125 views
  • 0 likes
  • 2 in conversation