03-06-2012 01:19 PM
I'm using the excelxp tagsets to create my excel file from SAS. However, I need to put a password in every generated file.
Can you help me???
03-06-2012 07:52 PM
When you use TAGSETS.EXCELXP, you re not creating a "true, binary" Excel file (.XLS or .XLSB, etc). You are creating an ASCII text file that conforms to the Spreadsheet Markup Language 2003 XML specification format for Microsoft Office 2003. Even if you name the file with a .XLS file extension, you are only "fooling" the Windows registry. What is INSIDE the file are still XML tags, that conform to the Microsoft XML specification for Office 2003. You can prove this to yourself by opening the file that ODS creates with Notepad. You will see XML tags inside the file.
So what does that mean? That means that the following features (highlighting mine) are not saved by Excel, if for example, you go into a workbook, do some typing, set a password and then try to save the file as 2003 XML, Excel gives you a message that says some features will not be saved. If you click help and look under XML Spreadsheet 2003 file format, you will see this message from Microsoft (cut and pasted from Microsoft message window):
"This XML Spreadsheet 2003 file format (.xml) does not retain the following features:
--Auditing tracer arrows
--Chart and other graphic objects
--Chart sheets, macro sheets, dialog sheets
--Data consolidation references
--Drawing object layers
--Outlining and grouping features
--Password-protected worksheet data
--User-defined function categories
So if there is no way to save password protected files to XML format using Excel, that means there is no way for ODS to create password protected XML files (created with TAGSETS.EXCELXP) -- because if the Microsoft XML 2003 specification will not support it, there's nothing ODS can do to make it be supported.
The only thing I can think of is for you to write some kind of VB script or Excel macro to post-process the file after it is created with ODS:
1) open the XML 2003 file created by ODS
2) resave as an XLSX file
3) set the password as you need in your script or macro
4) save again as XLSX
I can't think of any other way, (except for maybe DDE or OLE-DB) than what I suggest above. There's no way to set the password directly with ODS. There might be operating system methods to password protect the file, but I'm not really sure how that would work or whether it's better to set the password inside Excel versus at the operating system level.
03-06-2012 08:07 PM
Cynthia, there is another way as shown in the following paper. They use the tagset to create the file, then DDE to add the password: http://www.nesug.org/Proceedings/nesug11/gr/gr03.pdf
03-06-2012 10:13 PM
Thanks, Art. I did mention DDE in my post. I just didn't have the NESUG paper link. I usually avoid DDE, if I can because both SAS and Excel have to be open to run DDE and I'm not always comfortable with the Microsoft technology. But it is great to have the link!