BookmarkSubscribeRSS Feed
Obsidian | Level 7

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

options noxwait noxsync;
x "cscript ""&vbscript""";


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.

Check out the Boston Area SAS Users Group (BASUG) video archives:
Super User

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.


Barite | Level 11



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


- Cheers -

Obsidian | Level 7

Thank you for the responses! Looking into these now. 



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4 in conversation