The SAS Output Delivery System and reporting techniques

adding cellprotection in Excel for certain cells

Reply
N/A
Posts: 1

adding cellprotection in Excel for certain cells

Hi, I'm trying to protect a certain range of cells in an Excel worksheet via SAS, but it won't work out. Any hints?
SAS Super FREQ
Posts: 8,740

Re: adding cellprotection in Excel for certain cells

Hi:
My question is "how" are you trying to accomplish the protection?

I don't think it can be done with PROC EXPORT or the Excel LIBNAME engine or with ODS CSV, ODS HTML or ODS TAGSETS.EXCELXP.

Possibly with DDE or ODBC or OLE-DB, you might be able to send the right commands from SAS to Excel to protect the cells. But the commands you would send would be Excel commands.

If you are using the SAS Enterprise Intelligence Platform and the SAS Add-in to Microsoft Office, there is a way, under Options, to protect the results of a Stored Process, but since you posted your question in the ODS forum, I suspect you are not using the Platform.

cynthia
Valued Guide
Posts: 2,174

Re: adding cellprotection in Excel for certain cells

much depends on what you need, but simple protection can be switched on in the XML with something like[pre] [/pre]instead of[pre] [/pre]
These are selected out of XML files created by saving from excel ~ one with and one without protection.
Providing that "protection flag" update in an XML text file should be straight forward data step code.

I noticed that Excel2003 won't allow a password to be saved in an XML format workbook.

To keep only certain areas protected is not really the way excel works.
Protection applies to whole sheet/book. All areas are protected, except those formatted as "unlocked". Excel creates additional styles for unprotected(unlocked) areas. In my simple test excel created the style [pre] [/pre] that switches off protection for the cells using that style, as in the example[pre] a1[/pre]

The addition of protection looked simple when applying to the whole sheet, but much more complex for just parts of the sheet. Without a password, the protection has to be providing some convenience or (I imagine) users will just remove the protection.

hope this helps

PeterC
Contributor
Posts: 47

Re: adding cellprotection in Excel for certain cells

I'm also trying to "protect" or "lock" a range of columns (but not the entire sheet) in Excel output. Does anyone have a solution? Because the sheets are going out to people with a variety of versions of Excel, I need to not use the excelxp tagset. I'd rather not post-process if I don't have to.

One possibility I considered is to have a HIDDEN id embedded that would link back to the original data provided, so that it didn't matter if anything was overwritten.

TIA
SAS Super FREQ
Posts: 8,740

Re: adding cellprotection in Excel for certain cells

Hi:
The only 2 SAS/ODS choices I can see for this are 1) to try to implement a TAGSETS.EXCELXP solution by modifying the tagset template to protect the whole sheet; or 2) post-process the TAGSETS.EXCELXP file with a DATA step program to put the protection info in, as shown in Peter's previous post -- at the <Cell> level.

However, you said that you didn't want to post-process and you didn't want to use TAGSETS.EXCELXP.

There might be an Excel macro method or VBScript method -- where you'd create the file with ODS HTML and then applied the macro or VBScript -- but that means every person who opened the file would need the macro -- or you'd have to post-process the files with the macro or VBScript and then resave the files before sending them out.

The thing that I worry about some kind of hidden column or hidden ID is that once a person opens the file in Excel, if the file's not protected, then they could delete or change anything in the file.

Basically, I think that SAS and ODS are not designed to interact with pull-down menus in the rendering applications (like Word or Excel). The reason that some pulldown menu settings (like orientation or auto-filter) are possible with TAGSETS.EXCELXP are that Microsoft allows those settings to be provided via XML tags. Of course, this means that the users would all need Office2002/2003 to open and render the ODS-created file.

Not a lot of suggestions. Maybe someone else has an idea.

cynthia
Contributor
Posts: 71

Re: adding cellprotection in Excel for certain cells

One trick when working with the ExcelXP tagset is to do get the result you want in Excel first, i.e. protect a range of cells on a sample worksheet, then save the workbook as XML.

Once that's done, view the XML text file to see what controls (if any) Excel adds to protect the cells.

From here you might be able to modify your ODS ExcelXP SAS statements to insert the controls via TAGATTR or some other way.

Editing the tagset itself might be necessary, but this is a substantial undertaking just to get your head around the logic, let alone add new functionality without breaking something else (and always create a new version of the tagset when making changes).

Regards
Boschy
New Contributor
Posts: 2

Re: adding cellprotection in Excel for certain cells

Hi All,

Thanks for the above information. However, I have a question here- I am not able to put password on excel files using SAS. I also tried this code posted on th link http://support.sas.com/kb/31/328.html, but it is not able to protect the excel file with passwords. If any one has any idea regarding this , please share your views.

Many thanks for your efforts in advance.

Warm regards
Pradeep
SAS Super FREQ
Posts: 8,740

Re: adding cellprotection in Excel for certain cells

Hi:
The information posted on that link was for a DDE solution. If the password protection did not work, are you certain that you had the correct DDE syntax for applying password protection for the level of Excel that you were creating??

You might wish to open a track with Tech Support on this question, or investigate the Microsoft documentation on DDE commands.

cynthia
New Contributor
Posts: 2

Re: adding cellprotection in Excel for certain cells

Thanks very much Cynthia for the response.
Ask a Question
Discussion stats
  • 8 replies
  • 1172 views
  • 1 like
  • 6 in conversation