DATA Step, Macro, Functions and more

how to run VBA macro through SAS

Reply
Occasional Contributor
Posts: 15

how to run VBA macro through SAS

Hi Team,

 

Can you please advise me how to run the VBA Macro through SAS 9.4,

 

 

Thanks & regards 

A Ganesh

PROC Star
Posts: 1,215

Re: how to run VBA macro through SAS

Posted in reply to ganeshsas764
Occasional Contributor
Posts: 15

Re: how to run VBA macro through SAS

Thank you,

But I want only execute the VBA macro, no need to export any data to excel
file.
Super User
Posts: 23,323

Re: how to run VBA macro through SAS

Posted in reply to ganeshsas764

DDE. Use google to find examples.

Occasional Contributor
Posts: 15

Re: how to run VBA macro through SAS

Hi ,

 

I tried below-mentioned code but I am getting error,

 

options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\Office\excel.exe" ';
/* Sleep for 5 seconds to give Excel time to come up */
data _null_;
x=sleep(5);
run;
filename cmds dde 'excel|system';
data _null_;
file cmds;
/* Open the excel file test.xlsm which contains the VBA macro */
put '[open("c:\temp\test.xlsm")]';
/* Run myVBAmacro */
put '[run("test.xlsm!myVBAmacro")]';
run;

 

Super User
Posts: 13,338

Re: how to run VBA macro through SAS

Posted in reply to ganeshsas764

ganeshsas764 wrote:

Hi ,

 

I tried below-mentioned code but I am getting error,

 

 


When you get errors it really helps to share the actual error. Best is to copy the code submitted with the error and other messages from log and paste into a code box opened using the forum {I} icon. The code box will preserve formatting of error messages.

 

Note that DDE may be interfered with by other programs that are running on your computer. I know that Cisco Jabber is one and that the process, not just  the application window, have to be killed before DDE will work on my computer.

PROC Star
Posts: 8,149

Re: how to run VBA macro through SAS

Posted in reply to ganeshsas764

If @draycut is right, and you are looking to run VB script from SAS, you can run it by using:

call system(yourVBscript.);

The updated version of the paper that @draycut mentioned, which is a SAS macro that both creates and runs VB script, can be found at: http://www.sascommunity.org/wiki/Excelling_to_Another_Level_with_SAS

 

Art, CEO, AnalystFinder.com

 

Ask a Question
Discussion stats
  • 6 replies
  • 204 views
  • 1 like
  • 5 in conversation