Date: Thu, 29 May 2003 14:12:11 -0700
Reply-To: "William W. Viergever" <wwvierg@ATTGLOBAL.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "William W. Viergever" <wwvierg@ATTGLOBAL.NET>
Subject: Re: How to execute Excel 2002 VBA Macro?
In-Reply-To: <CCsBa.3983$ME.691876@news20.bellglobal.com>
Content-Type: text/plain; charset="us-ascii"; format=flowed
At 02:55 PM 05/29/2003 -0400, JHong wrote:
>Hi William,
>
>Thanks for your help. Your code snippet works fine.
>
>My original code started the dde session like this:
>
>filename sas2xl dde 'excel | example.xls!r2c1:rc2c3; notab;
>
>data _null _ ;
> file sas2xl ;
> put 'May results' ;
>run;
>
>data _null_ ;
> file sas2xl ;
> put '[run("example.xls!macro1")]' ;
>run;
>
>because I needed to enter data into the cells prior to executing a macro to
>bold the fonts and widen the column.
>Instead of executing macro1, the string 'run("example.xls!macro1"]' got
>pasted in the cell.
>
>I guess my problem is that I must use :
>
>filename sas2xl dde 'excel | system' ;
Yes. The "triplet" is for data; the "duplet" (?) is for passing comands to
Excel.
>Best Regards,
>Jim
>
>"William W. Viergever" <wwvierg@ATTGLOBAL.NET> wrote in message
>news:5.2.1.1.2.20030529100047.00af8ac0@pop3.attglobal.net...
> > At 10:49 AM 05/29/2003 -0400, JHong wrote:
> >
> > >Hi All,
> > >
> > >I need to execute a VBA macro in an Excel 2002 workbook and then close
>down
> > >Excel.
> > >
> > >Unfortunately the only sample code that I've able to locate is XL4M based
> > >and it doesn't seem to work with Excel 2002.
> > >
> > >I'm using SAS 8.02.
> > >
> > >Thanks,
> > >Jim
> >
> >
> >
> >
> > Jim:
> >
> > You can use the XL4M RUN comand to do a more current VBA macro via DDE.
> >
> > e.g.,
> >
> > filename cmds dde 'excel|system' ;
> > data _null_ ;
> > file cmds ;
> > put '[RUN("test_SAS_run_Excel_macro.xls!Sort")]' ;
> > run ;
> >
> >
> > ... this code runs a VBA macro called SORT in an Excel 2002 workbook
>called
> > test_SAS_run_Excel_macro.xls
> >
> > Note: this assumes the worksbook is already open and the SORT macro,
> > obviously, already written.
> >
> > For further info, check the SAS-L archives for posts on this subject by
> > Kilo Volt, Koen Vyverman, Nancy Brucken, Howard Schreier, and others:
> >
> > http://www.listserv.uga.edu/archives/sas-l.html
> >
> >
> > Later
> >
> >
> >
> >
> > ------------------------------------------------------------
> > William W. Viergever Voice : (916) 483-8398
> > Viergever & Associates Fax : (916) 486-1488
> > Sacramento, CA 95825 E-mail : wwvierg@attglobal.net
> > ------------------------------------------------------------
------------------------------------------------------------
William W. Viergever Voice : (916) 483-8398
Viergever & Associates Fax : (916) 486-1488
Sacramento, CA 95825 E-mail : wwvierg@attglobal.net
------------------------------------------------------------
| 
