Can you protect a Excel worksheet from within a SAS procedure or data step?

[chandler]

I use SAS for Windows, v9.2 TS2M3, on Windows XP Professional operating system.   I have some report requests, to be ouput via ODS to Excel spreadsheets (preferably Excel 2007), but the sheets need to be password protected to prevent modifications to the reports.  Is there a way to password protect a output Excel spreadsheet, but do it from SAS, not manually from Excel, after-the-fact?

[data_null__ · Accepted solution]

This program opens an XML file in Execl and saves it as an XLSX with the

WriteResPassword

The write-reservation password for this file. If a file is saved with the password and the password is not supplied when the file is opened, the file is opened as read-only.

This way it can be opened readonly without know anything.  I like not having to know anything.

data _null_ / pgm=work.xml2xlsx;
   /* gather info */
   length xml xlsx $256 script filevar command $256;
   xml = pathname('FT67F001');
   xlsx = xml;
   substr(xlsx,find(xml,'.',-length(xml)))='.xlsx';
   put xml= xlsx=;
   script   = catx('\',pathname('WORK'),'XML2xlsx.vbs');
   filevar  = script;

   /* write the script */
   file dummy1 filevar=filevar;
   put 'Const ' xml=$quote256.;
   put 'Const ' xlsx=$quote256.;
   *put 'Const xlCSV = 6';
   put 'Const xlWorkbookNormal = 51';
 

   put 'Set objExcel = CreateObject("Excel.Application")';
   put 'With objExcel';
   put +3 '.Visible = False';
   put +3 '.DisplayAlerts = False';
   put +3 'Set objWorkbook  = .Workbooks.Open(xml)';
   put +3 'objWorkBook.SaveAs xlsx,xlWorkbookNormal,"","WritePass"';
   put +3 '.Application.Quit';
   put +3 'End With';
 

   /* close the script file by opening another, not used */
   filevar = catx('\',pathname('WORK'),'DUMMY.vbs');
   file dummy1 filevar=filevar;
   /* look at the script, not necessary but may be useful */
   infile dummy2 filevar=script end=eof;
   do _n_ = 1 by 1 while(not eof);
      input;
      putlog _n_ 3. +2 _infile_;
      end;
   /* call the script */
   command = catx(' ','cscript',quote(strip(script)),'//nologo');
   infile dummy3 pipe filevar=command end=eof truncover;
   do while(not eof);
      input;
      putlog _infile_;
      end;
   stop;
   run;

filename FT67F001 'testXP.xml';
ods results off;
ods tagsets.excelxp file=FT67F001;
proc print data=sashelp.class;
   run;
ods tagsets.excelxp close;
 
data pgm=work.xml2xlsx;
   run;

The stored program can be saved in a library and called simply as data pgm=work.xml2xlsx;  The only required input is the FILEREF FT67F001.

[chandler]

Thank you so much, "data_null;"   .

I will be producing multiple separate worksheets, each filtered on a different variable in the file, using PROC REPORT.  Where in the program, do I need to place the call to this stored program ?

[data_null__]

chandler wrote:

Thank you so much, "data_null;"   .
I will be producing multiple separate worksheets, each filtered on a different variable in the file, using PROC REPORT.  Where in the program, do I need to place the call to this stored program ?

I'm not sure I understand "multiple separate worksheets".  I do understand "multiple separate workbooks" aka an excel file.  Notice in the sample program I used a fileref FT67F001 to define a new workbook.  Then I created some output in that workbook.  It could have been multiple sheets but it wasn't.  The output I create was excel(XML) using tagsets.excelxp.  Is that how your are creating your workbooks?

filename FT67F001 'testXP.xml';

ods results off;

ods tagsets.excelxp file=FT67F001;

proc print data=sashelp.class;

   run;

ods tagsets.excelxp close;

Once the file has been written and closed then you can call the stored program.  Also note this converts the XML to XLSX and adds the password. If you want to use the program as a stored program you will need to "read up" on that.  Or you could just make the data step a macro.  This might be easiest if you have other autocall macros already set up for your project.  If this doesn't answer your question let me know.

data pgm=work.xml2xlsx;

   run;

[R_Anderson]

The code worked like a charm however, how can I remove  'read only' . I only want the excel document to be password protected.