BookmarkSubscribeRSS Feed
dprasa8
Calcite | Level 5

Hi All -

I am trying to automate the steps of process flow which includes SAS and SAS EG tasks(plug-ins) and Excel VBA macros.

Here i would need help to understand how to run  a macro which is located in  excel file.

Example: sample.xlsm file  has the  macro with the name 'my_macro' .

I would need to run this in windows command line by using .bat file.

I have done some googling I could not find proper solution.

Thanks in advance!!

Thank You,

Durga.

4 REPLIES 4
user24feb
Barite | Level 11

Proceed as for example described here: http://www.lexjansen.com/pharmasug/2005/CodersCorner/cc21.pdf

Your code could look like:

Options XSync;

Data _NULL_;

  rc=System('Start Excel');

  x=sleep(3);

run;

Filename EXCEL DDE 'EXCEL|SYSTEM';

Data _NULL_;

  File EXCEL;

  Put '[Open("<Path>\sample.xlsm")]';

  Put '[Run("My_Macro")]';

  Put '[Quit()]';

Run;

Filename EXCEL Clear;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Do be aware though, user24feb, DDE is old technology, no longer supported by M$.  It may/may not work in future, and wouldn't have all the functionality of later (at least 2007/2013 version) releases.

ballardw
Super User

My organization has identified at least one application, Cisco Jabber, that will interfere with SAS DDE connections to Excel and other "legacy" software. This can be worked around by killing the process using Task Manager (closing the application leaves a process running). I wouldn't be surprised to find other applications that do the same.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

You would need to investigate VB scripting.  In your VB script you would need to open an office object.  Then you can use that object to execute various things which would normally be done within Office applications.

Here are some links to help you however this is out of scope of a SAS forum:

http://www.pcreview.co.uk/forums/opening-excel-spreadsheet-within-vbscript-t965331.html

http://answers.microsoft.com/en-us/office/forum/office_2010-customize/excel-fails-to-open-in-vbscrip...

Or from Visual Basic app:

https://support.microsoft.com/kb/219151?wa=wsignin1.0

http://www.thescarms.com/vbasic/ExcelExport.aspx

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 8162 views
  • 3 likes
  • 4 in conversation