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