BookmarkSubscribeRSS Feed
remco
Calcite | Level 5
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?
8 REPLIES 8
Cynthia_sas
SAS Super FREQ
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
Peter_C
Rhodochrosite | Level 12
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
louisehadden
Quartz | Level 8
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
Cynthia_sas
SAS Super FREQ
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
boschy
Fluorite | Level 6
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
pradeepohm
Calcite | Level 5
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
Cynthia_sas
SAS Super FREQ
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
pradeepohm
Calcite | Level 5
Thanks very much Cynthia for the response.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3395 views
  • 1 like
  • 6 in conversation