Create password protetcted excel sheet

[ambadi007]

Hi Team,

I need to create a password protected report for one of my customer and I use the below code to sent the report through email . Is there any way If I can make my report a password protected it would be great If i could include with this macro

%macro emailreport(path=, name=, ext=, date=, format=, to=, cc=, msg=, sig=, attach=);

FILENAME mail EMAIL 'nul' ;

DATA _NULL_;

FILE mail ;

subject = &name;

tolist = tranwrd(trim('(' || quote(&to) || ')'), ';', '" "');

cclist = tranwrd(trim('(' || quote(&cc) || ')'), ';', '" "');

msg=&msg;

sig=&sig;

datestr = put(&date, &format);

attachment = &path || &name || ' ' || datestr || '.' || &ext;

put '!EM_TO!' tolist;

put '!EM_CC!' cclist;

put '!EM_SUBJECT!' subject;

if (&attach) then

put '!EM_ATTACH!' attachment;

if (missing(msg) = 0) then

put msg;

if (missing(sig) = 0) then

put sig;

/*put '!EM_SEND!';*/

run;

%mend emailreport;

Thanks

Manesh

Mob +91 9048904821

[RW9]

Sorry, how is this related to Excel per your title, this is an email generation macro?  I don't think there is currently a way of creating Excel files which have password protection, although I would advise that that type of password protection is not considered good anyway.  Also, email is not a good method of transfering reports, it can be unsecure, and reports can be too big for email.  Consider using a proper medium - access controlled portal for instance and post reports directly there.

[ambadi007]

Hi

The application which I handle use email to transfer the reports and these reports are not big in size .  The below code which I use to craete an excel file and now need a password protection with this macro. The customer needs that . is there any way to include a passwd macro for passwrd creation?

%macro exportreport(path=, name=, date=, format=, data=);

data _NULL_;

src=&path || &name || '.xlsx';

src2 = &path || &name || '.xls';

dest = &path || &name || ' ' || put(&date, &format) || '.xlsx';

dest2 = &path || &name || ' ' || put(&date, &format) || '.xls';

if (fileexist(src)) then

do;

call system('copy ' || quote(src) || ' ' || quote(dest));

call symput('_exportreportpathname', dest);

end;

else if (fileexist(src2)) then

do;

call system('copy ' || quote(src2) || ' ' || quote(dest2));

call symput('_exportreportpathname', dest2);

end;

run;

%exportfile(pathname=&_exportreportpathname., data=&data);

%mend exportreport;

[RW9]

Sorry, there is no creation in that code, all it does is copy a file from somewhere.  Anyways, as I said before email is not a good medium for transmitting data, nor is Excel.  There are a multitude of solutions out there.  As for your particular problem, I am not aware of a way to password protect a spreadsheet (unless you want to go with DDE which is very old and likely wont work), nor a way to password protect an email.  

[Reeza]

There's no SAS mechanism at present to create a password protected file. You'll need VBA or DDE and I Dont think I've seen DDE code to do it so far. 

[OddCat64]

Hi,

would you or anyone out there happen to know the syntax of adding password protection to this piece of code - as created in the footnote of a proc report.  Apologies for any extra apostrophies.

footnote j=1 'input onclick="document.execCommand(' 'SAVEAS '',true,

' ' c:\\temp\\test.xls' ')" value=Download to Excel"

type="button">';

My colleague discovered this on some documentation somewhere and we cannot find the rest of it

The process is possible in dde but I am only aware of it being used in the local environment of base sas where it is as simple as writing password=   I am trying to dig out syntax now but, I have not used this for about 10 years

[Reeza]

That doesn't look like DDE, I suspect it's VBA...it depends on what else is in there. 

Can you please start a new thread with more details?

[OddCat64]

no it isn't dde, I too assume vba
will start new thread when I work out how, sorry I am a newby to this.

[OddCat64]

no, its java script and - and I am not getting anywhere on the java forums either 🙂

[RW9]

It isn't Java, M$ products use VBA, Java is Oracle.  As you will see from my first post on the subject, password protection on Excel files is not sufficient in any sense, nor is the the use of Excel for anything other than reports you have to generate.  First, if you are transferring data, then use a plain text format which is cross platform non proprietary, like CSV or XML.  Then use FTP or secure server for transmittal of the data so it is auditable and traceable.  

If it is a report your producing and you have to use Excel (in fairness any other format is better than Excel) then it should also goto a secure server or web portal of some kind.  Just password protecting it is not secure, nor is it user friendly (which is the only plus of Excel).

[Reeza]

http://support.sas.com/kb/48/682.html

If your using ODS TAGSETS This may be helpful. It could be modified to work on a native Excel file. 

[OddCat64]

Thanks that looks useful 🙂

[LinusH]

Password protected files is a scourge. And a result of using push reports.
If you use pull instead you can restrict user access on the server.