BookmarkSubscribeRSS Feed
Corinthian94
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';
run;

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

4 REPLIES 4
Quentin
Super User

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.

The Boston Area SAS Users Group (BASUG) is hosting an in person Meeting & Training on June 27!
Full details and registration info at https://www.basug.org/events.
ballardw
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.

 

Oligolas
Barite | Level 11

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

________________________

- Cheers -

Corinthian94
Obsidian | Level 7

Thank you for the responses! Looking into these now. 

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1564 views
  • 0 likes
  • 4 in conversation