4 users online | 4 Guests and 0 Registered

Can I create a password-protected Excel spreadsheet using tagsets.excelxp?

No the XML file generated by the ExcelXP tagset does not support passwords, however the following code can generate a VB script which can be called to convert the XML file to a native Excel file, and add a password.

/* Create a test worksheet to password protect */ 
ods tagsets.excelxp file="c:\temp.xml";                                                                                                 
proc print data=sashelp.class;                                                                                                          
ods tagsets.excelxp close;                                                                                                              
/* Create and excecute a script file using the input XML file    */                                                                           
/* and the converted XLSX file. The value 51 is needed for XLSX. */                                                                           
/* Use the value of 1 for XLS files in the SaveAs command.       */ 
%let XMLfile = c:\temp.xml;                                                                                                             
%let ExcelFile = c:\temp.xlsx;                                                                                                          
%let VBscript  = c:\convert.vbs;                                                                                      
%let password=test;                                                                                                                    
data _null_;                                                                                                                            
   file "&vbscript" lrecl=200;                                                                                                            
   put 'Dim xlApp, xlWkb, SourceFile, TargetFile';                                                                                         
   put 'Set xlApp = CreateObject("excel.application")';                                                                                    
   put 'SourceFile="' "&XMLfile" '"';                                                                                                      
   put 'Set xlWkb = xlApp.Workbooks.Open(SourceFile)';                                                                                     
   put 'TargetFile="' "&ExcelFile" '"';                                                                                                    
   put 'xlApp.DisplayAlerts=false';                                                                                                        
   put "xlWkb.SaveAs TargetFile, 51,""&password""";                                                                                          
   put 'xlApp.DisplayAlerts=true';                                                                                                         
   put 'xlWkb.close';                                                                                                                      
options noxwait noxsync;                                                                                                                
x "cscript ""&vbscript""";
Alan D Rudland
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.

Records in this category