LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (August 1997, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 12 Aug 1997 15:49:48 -0400
Reply-To:     "Childs, Randy" <RCHILD@ABTI.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Childs, Randy" <RCHILD@ABTI.COM>
Subject:      Re: ? on dde to excel, xlm and vis basic
Comments: To: dickinsn@UMICH.EDU
Content-type: text/plain; charset=US-ASCII

David, This is not an uncommon question. As far as converting Excel 4.0 Macro Language to VB, I can't say. I can, however, tell you how to use VB code in Excel from SAS. You have a couple of options on how to "write" the Visual Basic code and then AFAIK you have only this one option for running the macro. You may

choose to write your VB code directly into an ASCII file (say, using Notepad) or you may write the code to an external file from within SAS using DATA _NULL_

and PUT statements. Either way, this method requires a file that can be called into Excel using a VBA insert. The following code creates an Excel MODULE sheet, moves it to the end of the list of sheets in the workbook (optional), then writes the Visual Basic code to a file called C:\TEMP\MAC1.TXT, inserts this file into the MODULE sheet, and lastly executes the macro.

OPTIONS NOXWAIT NOXSYNC;

X 'C:\MSOFFICE\EXCEL\EXCEL.EXE';

DATA _NULL_; X = SLEEP(3); RUN;

FILENAME CMDE DDE 'EXCEL|SYSTEM';

DATA _NULL_; FILE CMDE; PUT '[WORKBOOK.SELECT("Sheet16","Sheet16")]'; PUT '[WORKBOOK.INSERT(6)]'; PUT '[WORKBOOK.SELECT("Module1","Module1")]'; PUT '[WORKBOOK.MOVE("Module1","Book1",18)]'; RUN;

FILENAME MACRO1 'C:\TEMP\MAC1.TXT';

DATA _NULL_; FILE MACRO1; PUT 'Sub Macro1()'; PUT 'Columns("A:B").EntireColumn.Select'; PUT 'Selection.ColumnWidth = 16'; PUT 'End Sub'; RUN;

DATA _NULL_; FILE CMDE; PUT '[VBA.INSERT.FILE("C:\TEMP\MAC1.TXT")]'; RUN;

DATA _NULL_; FILE CMDE; PUT '[WORKBOOK.SELECT("Sheet1","Sheet1")]'; PUT '[RUN("Book1!Macro1",FALSE)]'; RUN;

Hope this is helpful.

Randy Childs Alpha-Beta Technology rchild@abti.com


Back to: Top of message | Previous page | Main SAS-L page