06-22-2016 11:38 AM
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' ;
FILE mail ;
subject = &name;
tolist = tranwrd(trim('(' || quote(&to) || ')'), ';', '" "');
cclist = tranwrd(trim('(' || quote(&cc) || ')'), ';', '" "');
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
if (missing(sig) = 0) then
Mob +91 9048904821
06-22-2016 11:46 AM
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.
06-22-2016 11:55 AM
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=);
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
call system('copy ' || quote(src) || ' ' || quote(dest));
call symput('_exportreportpathname', dest);
else if (fileexist(src2)) then
call system('copy ' || quote(src2) || ' ' || quote(dest2));
call symput('_exportreportpathname', dest2);
06-22-2016 12:11 PM
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.
06-22-2016 12:45 PM
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.
09-11-2017 06:19 PM
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"
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
09-12-2017 04:04 AM
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).
06-23-2016 12:26 AM