Password protect XLS using XML

aspless · April 30th, 2008, 03:34 AM

Chaps,

Essentially what i am trying to father is if in XML you are able to set Excel password protection.

I am currently exporting some SQL data to excel using.

Code:

Response.ContentType = "application/vnd.ms-excel"

I am using basic worksheet protection shown below.

Code:

 <x:ProtectContents>True</x:ProtectContents>
     <x:ProtectObjects>True</x:ProtectObjects>
     <x:ProtectScenarios>True</x:ProtectScenarios>

My understanding of XML is very limited and as yet no searches have produced any XML specific password setting.

If there is a method within XML that would be superb.

Cheers

Aspless

samjudson · April 30th, 2008, 03:44 AM

This can't be done if you are using the Excel XML format (at least in Excel 2003 that I tried it).

If you set a password on an excel spreadsheet and then try to save it as an XML Spreadsheet it tells you:

"A sheet in the workbook you are trying to save is password protected. Password protected sheets cannot be saved in an XML Spreadsheet. To save this file as an XML Spreadsheet, you must first remove the password".

/- Sam Judson : Wrox Technical Editor -/

samjudson · April 30th, 2008, 03:45 AM

Oh, and also, this shouldn't really be posted to the XML forum, its more of an Excel question.

/- Sam Judson : Wrox Technical Editor -/

aspless · April 30th, 2008, 05:02 AM

Thanks samjudson,

looking at your reply i realise i have not explained my function in enough detail.

I am currently using a classic asp page to export SQL data to excel using -

Code:

filename = Date() &  "-Results.xls"
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename="&filename&""

After investigating some simple XML examples i have been able to protect the worksheets within the <x:WorksheetOptions> tags using

Code:

<x:ProtectContents>True</x:ProtectContents>
     <x:ProtectObjects>True</x:ProtectObjects>
     <x:ProtectScenarios>True</x:ProtectScenarios>

What i am trying to get clarification of is if within XML you can set the password for the excel workbook.

If you feel this forum post is still more suited to the Excel forums please let me know.

Thanks

Aspless

samjudson · April 30th, 2008, 05:35 AM

It doesn't matter what you are calling the document (you could call it "spreadsheet.foo" if you wanted, as long as Excel knows to open .foo files it will open in Excel).

The content of the file appears to be XML, therefore it is an Excel XML Spreadsheet - therefore my previous answer stands.

/- Sam Judson : Wrox Technical Editor -/

aspless · April 30th, 2008, 05:44 AM

Thanks for the confirmation.

Aspless

joefawcett · April 30th, 2008, 06:17 AM

This can be done in Excel 2007 I believe.

The easy way to check appears to be to create a simple workbook and save it without a password. Then compare to the same document that is protecetd and passworded.

--

Joe (Microsoft MVP - XML)