CODEWITHSUNDEEP
sassas-macro
Mr Bunny
Mr Bunny

Reputation: 1

SAS EG Export Excel with pass

How can I protect my excel file with a password, because in sas EG not supported DDE.

I'm using this, but I dont have the convert.vbs, so where can i get it?

/***********************************************/                                                                                                                                       
/* Create a test worksheet to password protect */ 
/***********************************************/                                                                                                      

ods tagsets.excelxp file="c:\temp.xml";                                                                                                 

proc print data=sashelp.class;                                                                                                          
run;                                                                                                                                    

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';                                                                                                                      
run;                                                                                                                                    

options noxwait noxsync;                                                                                                                

x "cscript ""&vbscript""";

Upvotes: 0

Views: 914

Answers (2)

Reeza
Reeza

Reputation: 21274

If you don't have DDE it's unlikely that code will work for you either.

The last line, 

x "cscript ""&vbscript""";

Is a command to your operating system. Often if DDE is disabled, this type of functionality is also disabled. You can check this by examining the XCMD option.

proc options option=xcmd;
run;

If XCMD is enabled you'll see:

XCMD Enables the X command in SAS.

You can only change that setting at start up and sometimes need to be an administrator as well.

Upvotes: 0

user667489
user667489

Reputation: 9569

It appears that your code creates the file c:\convert.vbs for you and then runs it. You just need to remove the asterisks so that it's a valid file path.

Upvotes: 1

Related Questions