topic Re: Invoke VBA command or run VBA macro suning SAS EG in SAS Enterprise Guide

Invoke VBA command or run VBA macro suning SAS EG

anu_ha — Mon, 22 Jun 2015 12:44:41 GMT

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.

Re: Invoke VBA command or run VBA macro suning SAS EG

RW9 — Mon, 22 Jun 2015 13:34:57 GMT

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.

Re: Invoke VBA command or run VBA macro suning SAS EG

anu_ha — Mon, 22 Jun 2015 13:54:30 GMT

Sorry DDE is not available in SAS EG

Re: Invoke VBA command or run VBA macro suning SAS EG

CaseySmith — Mon, 22 Jun 2015 14:11:53 GMT

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

Re: Invoke VBA command or run VBA macro suning SAS EG

anu_ha — Mon, 22 Jun 2015 15:02:48 GMT

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