07-28-2016 10:25 AM - edited 07-28-2016 10:41 AM
I'm trying to export a dataset to a excel file that has a password protect feature enabled. Here is the code that I am using:
PROC EXPORT DATA = contactOUT OUTFILE = "S:\counselor.xlsx" DBMS=xlsx REPLACE; Sheet = "Counselor"; QUIT;
I get this error when I try to run it:
Error creating XLSX file -> S:\CommunityHealth\DPHO-Harlem\Research, Evaluation, and
Planning\Henry\EHACE\Reports\CHE Monthly Report\counselor.xlsx . It is either not an Excel
spreadsheet or it is damaged. Error code=8014900A
Requested Output File is Invalid
ERROR: Export unsuccessful. See SAS Log for details.
Any way to input the password into the code so it can write to it?
07-28-2016 11:02 AM
It is nothing to do with the password protected Excel file as far as I can see, the password is only on opening the file that it is a problem, and you have specified REPLACE, so it should just overwrite what is there, assuming you have permission to write there, and that path exists and has no special characters in. I suspect that you dont have access to that area, or something similar, what software are you using - is it Enterprise Guide, Visual Analytics, University Edition etc? For UE, you paths should be unix style and relative.
Also to note in any circumstance the path:
S:\CommunityHealth\DPHO-Harlem\Research, Evaluation, and Planning\Henry\EHACE\Reports\CHE Monthly Report\counselor.xlsx
Is really not to be recommended. Paths really should not contain spaces, special characters (such as commas) as this just causes problems for programmers.
07-28-2016 11:11 AM
07-28-2016 11:21 AM
Ah yes, looking at the SAS guidance:
It does seem like it will try to replace the named range if it exists. Well, from memory, no you can't do much with a password protected Excel file. but you could just use the fdelete command to delete the file before exporting:
07-28-2016 02:36 PM
07-28-2016 03:13 PM
Thanks for the response. Do you have any suggestions as to how to better protect the information if I remove the password? I export this data out to the excel file on a daily basis and only want a select number of people to be able to view the file. It's more a safe guard that someone does not accidently open the file being exposed to the information.
07-28-2016 04:06 PM
Don't use Excel full stop. Totally unvalidated, unsafe, unstructured file format. Publish your results to an access restricted web address, Sharepoint for example. Use PDF or something to make it hard to edit.