topic Re: need to create password protect excel file/ csv from data set. in New SAS User

need to create password protect excel file/ csv from data set.

Hema_12 — Thu, 05 Sep 2019 13:03:05 GMT

Description:-

I have one dataset called abc.sas7bdat and i need to export as excel/csv with password protected.

I have gone through different post but not getting proper solution.

ods tagsets.excelxp file="/SASCommon/test/DEBT_2019-07-31.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 = /SASCommon/test/DEBT_2019-07-31.xml;
%let ExcelFile = /SASCommon/test/DEBT_2019-07-31.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""";

I got this code on sas support website but I am not able to understand the cscript in X command.

please help me and give me some solution what is this cscript.

Thanks in advance.

Re: need to create password protect excel file/ csv from data set.

tomrvincent — Thu, 05 Sep 2019 13:07:13 GMT

I think it would be faster, simpler and safer to just open the file in Excel and add the password.

Re: need to create password protect excel file/ csv from data set.

SASKiwi — Fri, 06 Sep 2019 02:00:25 GMT

cscript is a Microsoft program for running scripts on MS Windows servers:

https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/cscript

It appears you are trying to run this on a Unix server so I doubt it will work.

Re: need to create password protect excel file/ csv from data set.

Hema_12 — Fri, 06 Sep 2019 10:37:56 GMT

Thanks for the help.

I appreciate your solution and i know this trick but for repeated work, I should not use your solutions. 😉