DATA Step, Macro, Functions and more

Create password protetcted excel sheet

Reply
Contributor
Posts: 60

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,401

Re: Create password protetcted excel sheet

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: 60

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,401

Re: Create password protetcted excel sheet

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: 17,837

Re: Create password protetcted excel sheet

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. 

Super User
Posts: 17,837

Re: Create password protetcted excel sheet

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. 

 

Super User
Posts: 5,257

Re: Create password protetcted excel sheet

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
  • 6 replies
  • 325 views
  • 0 likes
  • 4 in conversation