Invoke VBA command or run VBA macro suning SAS EG

[anu_ha]

Is there any way of running VBA macro using SAS EG. I know in base sas it can be done using DDE. But how to do that using SAS EG. I am using EG 5.1.

[RW9]

Hi,

VB for Applications is a programming environment within the office application.  DDE is a connector with which you can send code into an open Office Component.  You should be able to do the same in EG, though I don't use it.

I would however question why you would want to do this.  If a VBA macro needs to be run, set it to run On_Open, that way it will be run once the file is opened, rather than the need to push it to do it immediately.  Otherwise a more complete image of what you are trying to achieve is needed.

[anu_ha]

Sorry DDE is not available in SAS EG

[CaseySmith]

You cannot use DDE from EG (which connects to "headless" SAS sessions), since DDE relies on Windows messages and requires the SAS session to run in a windowing environment (and on the same machine as the process it is communicating with).

However, you can run VBSCript from EG if X commands are allowed on your server.  For example:

/* write the vbs file */
data _null_;
file 'c:\temp\test.vbs';
put "Set objFSO=CreateObject(""Scripting.FileSystemObject"")";
put "outFile=""c:\temp\test.txt""";
put "Set objFile = objFSO.CreateTextFile(outFile,True)";
put "objFile.Write ""test string"" & vbCrLf";
put "objFile.Close";
run;

/* execute the vbs file */
X 'c:\temp\test.vbs';

[anu_ha]

Can excel Libname be used to invoke VBA macro in Excel?