how to export excel with protected password

[SalmaTas]

i want to export excel sheet with password, for example this is the data i want to export: 

data test;
input user $10.;
datalines;
user1
user2
user3
user4
user5
;
run;

and then i want to export it to the specific location:

proc export data=test
outfile="C:\Users\Data\test.xls"
dbms=excel replace;
run;

where do i insert the password part in the export? 

[RW9]

Hi,

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

You can't do it directly in the generation of the file.  

[SalmaTas]

ok, thanks

[jitendra_pandey]

VBScript method might work it one has to password protect the files manually.  This is not a fool-proof method for a batch execution where you have dozens of file to passowrd protect and distribute.  What if the VBScript does not at all run?  You will end-up distributing unprotected file.  SAS does not provide any guarantee.  Please read the fineprint.

I believe this is time when SAS should cease its reliance on these VBScripts and come up with native mehtods.  Examples are 1) Generating rich Excel reports, 2) Converting XLS (OpenXML document) to native XLS or XLSX file format, 3) Password protection of Excel files as metioned in this topic.

[RW9]

Please note the date on this topic is 2015.  Open a new question if you need further information.

As for your points:

"VBScript method might work it one has to password protect the files manually.  This is not a fool-proof method for a batch execution where you have dozens of file to passowrd protect and distribute.  What if the VBScript does not at all run?  You will end-up distributing unprotected file.  SAS does not provide any guarantee.  Please read the fineprint." - of course, VBA is third part and so not under SAS, nor is it actually mentioned in this thread?

"I believe this is time when SAS should cease its reliance on these VBScripts and come up with native mehtods.  Examples are 1) Generating rich Excel reports, 2) Converting XLS (OpenXML document) to native XLS or XLSX file format, 3) Password protection of Excel files as metioned in this topic." - SAS does not have a reliance on VB, it is the users have their own desires, much like any third party tool.  

SAS does provide various methods to generate rich Excel files - tagsets.excelxp/msoffice2k, libname excel, proc export etc.

Why shoud SAS, with no involvement in either tech, provide a conversion between OpenXML and Open Office?

Password protection on Excel files (at least some time back) was a joke.  Anyone relying on that should really think about their processes.  Secure documents should be sent by the appropriate methodology - SFTP for instance, web portals and such like, with proper encryption, access control and auditing.  

[jitendra_pandey]

My bad, I always thought VBA is owned by SAS Institute.  And oh, I never knew files generated from ExcepXP are in native Excel format and neither did I know that MSOffice2K supports all the formatting options that ExcelXP does.  And this is also new to me that libname Excel and PROC EXPORT do support all the formatting options that ExcelXP does!!

Password protection on Excel files was a joke with Microsoft Excel versions prior to 2007.  Note this, that Microsoft has been using AES-128 bit encryption with Excel 2007 and later so it not a joke anymore.  Now, that could just be one layer in securing your email report distribution in combination with 2) encrypting an entire email message at your corporate Exchange server and 3) using Transport Layer Security or TLS for transmission and this would not be a joke of slightest nature.

[RW9]

VBA - Visual Basic for Applications, is ownder by Microsoft and is part of thier Office Suite.

Tagsets output are XML, not native XLSX which is actually a ZIP containing XML and folders.  However this XML is perfectly readable in Excel, i.e. it knows what it is.  It has a lot of customisation.  MSOffice2k is the older varient which some still use.

libname excel is limited in what formatting, however can create native XLSX files (i meant the following one not libname)

ods excel is the newset technology, this can create native XLSX files with various options:

communities.sas.com/community/support-communities

You could also use SAS office plugin - no experience of that though.

To note, instead of sending files by email, post to a secure web portal - avoids large files being sent/blocked (and messing up inboxes) and adds all the necessary security in - with a newtork address you can just save files there and then email out that XYZ output is updated.