BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chandler
Fluorite | Level 6


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?

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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.

View solution in original post

4 REPLIES 4
data_null__
Jade | Level 19

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
Fluorite | Level 6

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__
Jade | Level 19

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
Calcite | Level 5

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

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
  • 4 replies
  • 6875 views
  • 2 likes
  • 3 in conversation