DATA Step, Macro, Functions and more

Create password protetcted excel sheet

Reply
Contributor
Posts: 64

Create password protetcted excel sheet

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

Super User
Super User
Posts: 7,970

Re: Create password protetcted excel sheet

Posted in reply to ambadi007

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.

Contributor
Posts: 64

Re: Create password protetcted excel sheet

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;

Super User
Super User
Posts: 7,970

Re: Create password protetcted excel sheet

Posted in reply to ambadi007

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.  

Super User
Posts: 19,820

Re: Create password protetcted excel sheet

Posted in reply to ambadi007

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. 

Occasional Contributor
Posts: 5

Re: Create password protetcted excel sheet

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

Super User
Posts: 19,820

Re: Create password protetcted excel sheet

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?

Occasional Contributor
Posts: 5

Re: Create password protetcted excel sheet

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.
Occasional Contributor
Posts: 5

Re: Create password protetcted excel sheet

no, its java script and - and I am not getting anywhere on the java forums either Smiley Happy
Super User
Super User
Posts: 7,970

Re: Create password protetcted excel sheet

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

Super User
Posts: 19,820

Re: Create password protetcted excel sheet

Posted in reply to ambadi007

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. 

 

Occasional Contributor
Posts: 5

Re: Create password protetcted excel sheet

Thanks that looks useful Smiley Happy
Super User
Posts: 5,431

Re: Create password protetcted excel sheet

Posted in reply to ambadi007
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.
Data never sleeps
Ask a Question
Discussion stats
  • 12 replies
  • 459 views
  • 4 likes
  • 5 in conversation