SAS/AF Software: Class Dictionary

Using the OLE Automation Class

Populating a Microsoft Excel Spreadsheet with SAS Data

The following example uses SCL code to populate a Microsoft Excel spreadsheet with data from a SAS data set:

LAUNCHXL: hostcl = loadclass('sashelp.fsp.hauto'); call send (hostcl, '_new', excelobj, 0, 'Excel.Application.5'); call send (excelobj,'_setProperty', 'Visible','True'); return;

CREATEWS: call send (excelobj,'_getProperty', 'Workbooks', wbsobj); call send (wbsobj, '_do', 'Add' ); call send (excelobj,'_getProperty', 'ActiveSheet', wsobj );

dsid=open('sasuser.class','i'); call set(dsid); fetch(dsid); nvar=attrn(dsid, 'NVARS'); nobs=attrn(dsid, 'NOBS');

do col=1 to nvar; call send (wsobj, '_compute', 'Cells',1, col,retcell); var=varname(dsid,col); call send (retcell, '_setProperty', 'Value' ,var); end; do while (rc ne -1); do row = 1 to nobs; do col = 1 to nvar; r=row+1; call send (wsobj, '_compute', 'Cells', r ,col, retcell); if vartype(dsid,col) eq 'N' then var=getvarn(dsid,col); else var=getvarc(dsid,col); call send (retcell,'_setProperty', 'Value' ,var); end; fetch(dsid); end; end; return;

QUITXL: call send (excelobj,'_getProperty', 'ActiveWorkbook', awbobj ); call send (awbobj, '_do', 'Close', 'False' ); call send (excelobj,'_do', 'Quit' ); call send (excelobj,'_term' ); return;

As you can see from this example, automating an application object requires some knowledge of the object's properties and methods. To help you decide which SCL commands to use for an Excel automation object, you can use the Macro Recorder in Excel to perform the task you want to automate and then look at the Visual Basic code that is generated. It is then relatively simple to map the Visual Basic code to comparable SCL statements and functions.

Here are some excerpts of Visual Basic code with their SCL equivalents:

Visual Basic code OLE Automation in SCL

Launch Excel and make it visible
Set excelobj = CreateObject("Excel. Application.5") excelobj.Visible = True

hostcl = loadclass('sashelp.fsp.hauto'); call send ( hostcl, '_new', excelobj, 0, 'Excel.Application.5'); call send (excelobj,'_setProperty', 'Visible','True');

Create a new worksheet
Dim wbsobj, wsobj As Object Set wbsobj = excelobj.Workbooks wbsobj.Add Set wsobj = excelobj.ActiveSheet}

call send(excelobj,'_getProperty', 'Workbooks', wbsobj); call send(wbsobj, '_do', 'Add'); call send(excelobj,'_getProperty', 'ActiveSheet', wsobj );

Set the value of a cell
wsobj.Cells(row + 1, col).Value = var

r=row+1; call send(wsobj,'_compute', 'Cells', r, col, retcell); call send(retcell,'_setProperty', 'Value' ,var);}

Close the Excel application object
excelobj.ActiveWorkbook.Close ("False") excelobj.Quit

call send(excelobj,'_getProperty', 'ActiveWorkbook', awbobj); call send(awbobj, '_do', 'Close', 'False'); call send(excelobj,'_do', 'Quit'); call send(excelobj,'_term');

Visual Basic code	OLE Automation in SCL
Launch Excel and make it visible Set excelobj = CreateObject("Excel. Application.5") excelobj.Visible = True	hostcl = loadclass('sashelp.fsp.hauto'); call send ( hostcl, '_new', excelobj, 0, 'Excel.Application.5'); call send (excelobj,'_setProperty', 'Visible','True');
Create a new worksheet Dim wbsobj, wsobj As Object Set wbsobj = excelobj.Workbooks wbsobj.Add Set wsobj = excelobj.ActiveSheet}	call send(excelobj,'_getProperty', 'Workbooks', wbsobj); call send(wbsobj, '_do', 'Add'); call send(excelobj,'_getProperty', 'ActiveSheet', wsobj );
Set the value of a cell wsobj.Cells(row + 1, col).Value = var	r=row+1; call send(wsobj,'_compute', 'Cells', r, col, retcell); call send(retcell,'_setProperty', 'Value' ,var);}
Close the Excel application object excelobj.ActiveWorkbook.Close ("False") excelobj.Quit	call send(excelobj,'_getProperty', 'ActiveWorkbook', awbobj); call send(awbobj, '_do', 'Close', 'False'); call send(excelobj,'_do', 'Quit'); call send(excelobj,'_term');

Chapter Contents
Previous
Next
Top of Page