Can someone a) explain to me how this code works and b) tell me why it won't add a pw to my file?

[Corinthian94]

Title says it. I'm trying to create a program to add a password to an excel file using SAS but haven't been able to get it to work today. It worked a couple of weeks ago but I cant remember how or if I changed anything. Any ideas? Also an explanation of how it works would be great as well. Thanks!

%let ExcelFile = H:\Password Code Test\Password Code Test 4.xlsx;
%let VBscript = H:\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="' "&ExcelFile" '"';
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""";

[Quentin]

You're using SAS to write a VB script and then execute it.

As a first step, are you able to use a text editor to write the VB script you want and get it working?  If so, can you post the code for the working VB script?

Like when using the macro language to generate SAS code, when you are using the SAS language to generate a VB script, as a first step it would be helpful to start with a working VB script.  Then once you have a working VB script, you can use SAS to generate the script.   This avoids the problem of having to debug in two languages simultaneously.  First you debug the VB script, then debug the the DATA step that generates the VB script.

[ballardw]

Describe how it "doesn't work" or better yet share the LOG from running the code.

Is the script file created?

Does it have the proper content?

Does the X command execute?

Is the H drive correct relative to where your SAS executes?

Note: it is not uncommon for some sites to disable the X command. So maybe your SAS admins changed that on you.

As for why any given VB script works/fails I suggest a site that deals with VB.

One thing that I do see that has a chance of causing issues is in this line:

put "xlWkb.SaveAs TargetFile, 51,""&password"""; 

The 51 in that line refers to a specific version and type of Office file. If your version of Office changes you may need to change that to be current with your version of Excel otherwise you run into a "the file type doesn't match the name" type of complaint from Excel.

[Oligolas]

Hi,

I've tested it exactly as you wrote it, it works like a charm.

My hypothesis are: 
- your admin restricted the use of vbs files

- The drive H is not resolved correctly, try to use the real Path where H is mounted to

[Corinthian94]

Thank you for the responses! Looking into these now.