Running VBScript via SAS Enterprise Guide to Edit Excel 'Sheet Name'

[AngSAS]

Hi SAS Users,

I was wondering if anyone has come across similar issue as I am; and if so, what the solution was.

I use SAS Enterprise Guide, and one of the task I am working on requires me to edit Excel 'Sheet Name'. I could not find any solution that can be scripted within SAS, so I created a VBScript that does the sheet rename task. When I run this VBScript via command prompt it works but when I try to run it via Enterprise Guide, it does not work (need this to run via SAS for automation). It does not throw any error but does not run either (i.e. does not rename the sheet).

I have tried following three methods that I found online, but same result with all of them.

Method 1 (running command line code using sysexec):

%sysexec cscript "\\filepath\filename.vbs";

Method 2 (creating bat file to run command line code, and using x command to run the batch file):

FILENAME script "\\filepath\filename.bat";

DATA __NULL__;

FILE script;

PUT 'cscript "\\filepath\filename.vbs"';

RUN;

X script;

Method 3 (running the pipe command to run batch file):

filename oscmd pipe '"\\filepath\filename.bat" 2>&1';

data _null_;

infile oscmd;

input;

put _infile_;

run;

I noticed that the Enterprise Guide version in my laptop is 64bit but the SAS Add-In for Microsoft Office is 32bit. Could that be the reason? [VBScript uses WScript.CreateObject("Excel.Application") fuctionality]

Or am I missing something?

[ChrisHemedinger]

When running a script with SYSTASK or FILENAME PIPE, it's SAS that runs the script, not EG.  Assuming your SAS is on Windows and can run OS commands, you might want to be explicit about which version of cscript to run:

 %windir%\SysWOW64\cscript.exe - 32-bit version of CSCRIPT.exe

Chris

[AngSAS]

Thanks Chris for your reply.

However, that did not seem to work either.

We ended up eliminating the need to open the Excel file all together by moving all the calculations to SAS from excel.