SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Exporting to excel using tagsets excelxp and protect the file with password

Reply
New Contributor
Posts: 2

Exporting to excel using tagsets excelxp and protect the file with password

Hi everybody!

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???

Thank you...

SAS Super FREQ
Posts: 8,719

Exporting to excel using tagsets excelxp and protect the file with password

Hi:

  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

--Custom views

--Data consolidation references

--Drawing object layers

--Outlining and grouping features

--Password-protected worksheet data

--Scenarios

--User-defined function categories

--VBA projects"

  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.

 

cynthia

Esteemed Advisor
Posts: 7,293

Exporting to excel using tagsets excelxp and protect the file with password

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

SAS Super FREQ
Posts: 8,719

Exporting to excel using tagsets excelxp and protect the file with password

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!

cynthia

New Contributor
Posts: 2

Exporting to excel using tagsets excelxp and protect the file with password

Thank you people...

I'll try to apply this in my project...

Regards!

Ask a Question
Discussion stats
  • 4 replies
  • 1683 views
  • 4 likes
  • 3 in conversation