Set password for excel

deleted_user — Thu, 03 Sep 2009 08:30:42 GMT

Hi,
Is it possible to set a password for Excel book from within SAS (Base or EG)?

Re: Set password for excel

data_null__ — Thu, 03 Sep 2009 13:23:08 GMT

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]

topic Re: Set password for excel in ODS and Base Reporting

Set password for excel

Re: Set password for excel