topic Create password protetcted excel sheet in SAS Programming

Create password protetcted excel sheet

ambadi007 — Wed, 22 Jun 2016 15:38:32 GMT

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

Re: Create password protetcted excel sheet

RW9 — Wed, 22 Jun 2016 15:46:49 GMT

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.

Re: Create password protetcted excel sheet

ambadi007 — Wed, 22 Jun 2016 15:55:37 GMT

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;

Re: Create password protetcted excel sheet

RW9 — Wed, 22 Jun 2016 16:11:08 GMT

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.

Re: Create password protetcted excel sheet

Reeza — Wed, 22 Jun 2016 16:45:22 GMT

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.

Re: Create password protetcted excel sheet

Reeza — Wed, 22 Jun 2016 16:49:02 GMT

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.

Re: Create password protetcted excel sheet

LinusH — Thu, 23 Jun 2016 04:26:05 GMT

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.

Re: Create password protetcted excel sheet

OddCat64 — Mon, 11 Sep 2017 22:19:20 GMT

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

Re: Create password protetcted excel sheet

Reeza — Mon, 11 Sep 2017 22:21:26 GMT

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?

Re: Create password protetcted excel sheet

OddCat64 — Mon, 11 Sep 2017 22:38:23 GMT

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.

Re: Create password protetcted excel sheet

OddCat64 — Tue, 12 Sep 2017 00:28:52 GMT

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

Re: Create password protetcted excel sheet

RW9 — Tue, 12 Sep 2017 08:04:09 GMT

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).

Re: Create password protetcted excel sheet

OddCat64 — Tue, 12 Sep 2017 20:45:39 GMT

Thanks that looks useful 🙂