topic Re: Run VBA subroutine through SAS in SAS Programming

Run VBA subroutine through SAS

MohitMac — Fri, 14 Nov 2014 07:30:54 GMT

Hi Guys,
Please help me out ASAP.
I was trying to run a vba subroutine by writing code in SAS. I followed no. of ways and took help from diffrent blogs as poted by no. of peoples on diffrent site.
I saved and excel file including a macro function in .xlsm (book1) n tried to run this file using sas. code i wrote on sas :

options xsync;

data _null_;

file excel;

put '[open("c:\book1.xlsm")]';

run;

data _null_;

file excel;

put '[run("macro1")]';

run;

but this didnt work. my sas code didn't give error, instead of this it got hanged out.

Somehelp me out plz. so that i can run my code as much as possible and can represent to my client.

note: my vba code has no. of subroutine,i tried on a smalll macro to try this but didnt get result.

Waiting for reply.

Thanks & Regards,

Mohit

Re: Run VBA subroutine through SAS

Kurt_Bremser — Fri, 14 Nov 2014 08:08:52 GMT

file excel;

assigns an output file to the data step for a file reference of the name "excel".

Do you have a

filename excel ........;

somewhere before that in your code? If so, post it.

Re: Run VBA subroutine through SAS

MohitMac — Fri, 14 Nov 2014 08:30:53 GMT

Hi Kurt,

Thanks for your valueable answer.

i declared this before running data step.

options xsync;

filename EXCEL DDE 'EXCEL|SYSTEM';

and then run the data step .
when i m not declaring filename , sas executing the data step without any error, but affect on excel file, while i am using this "filename" statement sas got stucked for a long time for the same code.

Please let me know the step , which should i follow.

Thanks & Regards,

Mohit

Re: Run VBA subroutine through SAS

Kurt_Bremser — Fri, 14 Nov 2014 08:39:13 GMT

According to

http://support.sas.com/documentation/cdl/en/hostwin/63285/HTML/default/viewer.htm#ddeexamples.htm

you should explicitly specify noxsync and noxwait; also, did you open the DDE server by starting Excel first?

Re: Run VBA subroutine through SAS

RW9 — Fri, 14 Nov 2014 09:37:50 GMT

Hi,

What exactly is it your trying to achieve by running a VBA macro from Excel? IMO I would suggest that you go one of these routes:

- ODS tagsets.excelxp - unless there is something specific not in the tagset, this is the easiest.

- If there is something specific then:

- Create your spreadsheet as you would like it, with the macro in place. Export your data from SAS as plain XLS file. Now in your template

spreadsheet import that data across into your template spreadsheet. This gives most flexibility as you can have VBA copy across and process

the data.

- Create your spreadsheet as you would like it and create named ranges for where the data should go. In SAS create a libname to the Excel file

(example: http://www2.sas.com/proceedings/sugi31/024-31.pdf) and copy your data into the libname.

- A final possibility is to learn Open Office format. XLSX is actually a ZIP file (change the extension) with some subdirectories and XML files

within it. You can yourself create these XML files and structure in SAS with data and put statements if you know what you are doing.

I would not suggest using DDE as it may/may not be supported in future and due to it being written for and Excel from years ago doesn't have full functionality. Plus if you have the script running in anything but locally installed with office then it will fail.