Starting a VB script from a sas program in EG 5.1

[alepage]

Hello,

I am trying to start a VB script from a SAS program in EG 5.1 and it never start by itself.

I have to go on windows explorer, select the folder where the vb script is copied and double click on it.

Here's the code I have found on the communities

* Run the generated program ;
data _null_;
  infile "cscript ""&dir\temp.vbs""" pipe;
  input;
  put _infile_;
run;

Does anyone could help me with that issue?

Regards,

Alain

[RW9]

Could be various things, none of which are debuggable from what you have posted.

First, remove the macro variable and put a path in its place, then copy the string to your command window and see if the command actually runs, i.e. your path could be wrong.

Also do you have command line access from SAS, in a lot of setups this is deactivated for security reasons.

Thirdly, what does temp.vbs actually do?  What information is in the command window that you want to pipe back?  

%sysexec cscript "a:\abc\temp.vbs";

Would seem the simplest way to just execute a VBA script?

[alepage]

Hello,

I have found the following code on the communties web site in order to transform xls Excel file into xlsx file.

/****************************************/

/* Create sample files to convert. */

/****************************************/

%let dir=\\Spsas002\dfs\DATA08\SEAR\ANAQUANT\Portrait\BTar2018\V1_Dépot\SORTIES\MS_CT_UNI\Nouveau dossier;

%let ext=xls ;

* Generate Visual Basic program ;

data _null_;

file "'&dir\temp.vbs'";

put "set xlapp = CreateObject(""Excel.Application"")";

put "set fso = CreateObject(""scripting.filesystemobject"")";

put "set myfolder = fso.GetFolder(""&dir"")";

put "set myfiles = myfolder.Files";

put "xlapp.DisplayAlerts = False";

put "for each f in myfiles";

put " ExtName = fso.GetExtensionName(f)";

put " Filename= fso.GetBaseName(f)";

put " If ExtName=""&ext"" then";

put " set mybook = xlapp.Workbooks.Open(f.Path)";

put " xlapp.Visible = false";

put " mybook.SaveAs ""&dir.\"" & Filename & "".xlsx"", 51";

put " mybook.Close";

put " End If";

put "Next";

put "xlapp.DisplayAlerts = True";

put "xlapp.Quit";

put "Set xlapp = Nothing";

put "strScript = Wscript.ScriptFullName";

put "FSO.DeleteFile(strScript)";

run;

* Show the generated program ;

data _null_;

infile "'&dir\temp.vbs'";

input;

put _n_ 4. +1 _infile_;

run;

* Run the generated program ;

data _null_;

infile "cscript ""&dir\temp.vbs""" pipe;

input;

put _infile_;

run;

The script never start by itself.  I have to got to &dir folder and double click on the script icon.

More over, I have try your solution and it does not work.

Regards,

Alain

[Kurt_Bremser]

What is the log output from your last data step that runs the cscript? If there's none, the script should have run OK.

Are you sure that the SAS workspace server runs on the same machine that you are looking at for the results of the script?

I suspect that you run the script on the SAS server while expecting that it runs on your desktop.

[RW9]

So, several things.

First, why use SAS to run VB to change XLS to XLSX?  Seems like a lot of faff.  Add a loop into the VBS and just run that (i.e. drop the SAS part) or just use VB in Excel to do this by opening and saving each file - simple and you only need one tech then.

Second, why the change from XLS to XLSX?  I know XLSX is the newer format, but if your converting datasheets, move them to something portable and readbale by everything (removes the need to do this in the future) something like CSV/XML

I am afraid "and it does not work" does not really relate anything to me, its likely that you don't have system access from SAS.

[Kurt_Bremser]

If your EG is part of a client/server setup, you can only start external commands from code on the server.

[ChrisBrooks]

The ability to shell out to the operating system and run system commands (such as exexcuting the type of script you describe) is often disabled by system administrators. If you run the following pirece of code

proc options option=noxcmd define value;
run;

You should get something like this in your log file telling you the setting for that option

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 61         
 62         proc options option=noxcmd define value;
 63         run;
 
     SAS (r) Proprietary Software Release 9.4  TS1M4
 
 Option Value Information For SAS Option XCMD
     Value: NOXCMD
     Scope: SAS Session
     How option value set: SAS Session Startup Command Line
 
 
 Option Definition Information for SAS Option XCMD
     Group= ENVDISPLAY
     Group Description: Windows and data display information
     Description: Disables the X command in SAS.
     Type: The option value is of type BOOLEAN
     When Can Set: Session startup (command line or config) only
     Restricted: Your Site Administrator can restrict modification of this option
     Optsave: PROC Optsave or command Dmoptsave will not save this option
 NOTE: PROCEDURE OPTIONS used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

If it is disabled (as in this example) you may have to consult your SAS Administrator to ask for it to be changed.

[alepage]

Here's the code you have suggeted to use:

proc options option=noxcmd define value;

run;

You will find below the log file:

1 %_eg_hidenotesandsource;

5 %_eg_hidenotesandsource;

22

23 proc options option=noxcmd define value;

24 run;

SAS (r) Proprietary Software Release 9.3 TS1M2

Option Value Information For SAS Option XCMD

Value: XCMD

Scope: SAS Session

How option value set: Shipped Default

　

Option Definition Information for SAS Option XCMD

Group= ENVDISPLAY

Group Description: Windows and data display information

Description: The X Command is valid in this SAS session

Type: The option value is of type BOOLEAN

When Can Set: Session startup (command line or config) only

Restricted: Your Site Administrator can restrict modification of this option

Optsave: PROC Optsave or command Dmoptsave will not save this option

NOTE: PROCEDURE OPTIONS a utilisé (Durée totale du traitement) :

temps réel 0.00 secondes

temps UC 0.00 secondes

25

26 %_eg_hidenotesandsource;

38

39

40 %_eg_hidenotesandsource;

43

Therefore, I think it should work.  Am I right?

If so, what can I do to make it working?

Regards,

Alain

[ChrisBrooks]

Yes that confirms that you have privileges to shell out and run external commands so we can eliminate that from the equation. Have you tried the suggestions from @RW9 in his first reply to your post? That's exactly what I would do next.