SAS Companion for the Microsoft Windows Environment

Automating OLE Objects and Applications

Some Windows applications provide a scripting language that allows you to control and update objects and external applications through automation. In SAS/AF software, you can use SAS Component Language (SCL) for OLE automation. Using SCL code to send instructions to the OLE object, you can update the object's data based on a user's actions in your SAS/AF application.

In SAS/AF software, you can automate:

OLE objects embedded in a FRAME entry, using the OLE class
OLE objects linked to a FRAME entry, using the OLE class
OLE applications not associated with a FRAME entry, using the OLE Automation class.

Using SCL, you can communicate with any OLE object or application that supports OLE automation as a server. In this communication, SAS acts as a client while the automation application acts as a server. The server provides OLE automation objects, which you can control with SCL code. Using SCL methods, you can send OLE methods to the server for execution. You can also get and set the properties of the objects you control. OLE automation servers can support multiple types of objects, each of which can have a unique set of methods and properties. The SCL methods you can use are listed in OLE Automation Class Methods and described in detail in Summary of OLE Class Methods.

Note: Do not confuse the SCL OLE automation methods (listed in the table) with the methods provided by the OLE automation server. In SAS/AF software, the _COMPUTE_ and _DO_ SCL methods provide access to the methods supported by the OLE automation server. Each OLE automation server supports different methods, but you must always use the _COMPUTE_ or _DO_ method in SCL to invoke them. (You can use subclassing to create new methods that encapsulate these. For an example, see OLE Automation Class Methods.) [cautionend]

OLE Automation Class Methods
OLE Automation Method Description

_COMPUTE_ invokes a method supported by the OLE automation server and returns a value

_DO_ invokes a method supported by the OLE automation server (with no return value)

_GET_PROPERTY_ retrieves the value of a property exposed by the OLE automation server

_GET_REFERENCE_ID_ returns the reference identifier of an object provided by the OLE automation server

_IN_ERROR_ returns an object's ERROR status

_NEW_ assigns an SCL identifier to an external instance of an OLE automation server

_SET_PROPERTY_ sets the value of a property exposed by the OLE automation server

***OLE Automation Class Methods***
OLE Automation Method	Description
_COMPUTE_	invokes a method supported by the OLE automation server and returns a value
_DO_	invokes a method supported by the OLE automation server (with no return value)
_GET_PROPERTY_	retrieves the value of a property exposed by the OLE automation server
_GET_REFERENCE_ID_	returns the reference identifier of an object provided by the OLE automation server
_IN_ERROR_	returns an object's ERROR status
_NEW_	assigns an SCL identifier to an external instance of an OLE automation server
_SET_PROPERTY_	sets the value of a property exposed by the OLE automation server

Note: The return values and arguments passed between the automation server and SAS using the OLE automation methods are passed by value, not by reference--including those arguments that the server defines as pass-by-reference. That is, the arguments contain actual static values, not pointers to values that you can modify. [cautionend]

Accessing Array Values Returned by the OLE Automation Server

The SAS System lets you access single-dimensional arrays that are passed back by the OLE automation server as a property or as a result of one of its methods. When the array is returned to SAS, SAS stores it in an SCL list.

For example, the following SCL code creates and populates a listbox in a Microsoft Excel worksheet and stores the contents of the listbox in an SCL list:

list=makelist();  /* create the SCL list */
   /* Add a Listbox in a worksheet */
call send(worksht, '_COMPUTE_', 'Listboxes',
          listbox);
call send(listbox, '_DO_', 'Add', 20, 50, 
          40, 100);
call send(worksht, '_COMPUTE_', 'Listboxes', 
          1, listone);
   /* Fill the Listbox with a range of */
   /* values from the worksheet        */
call send(listone, '_SET_PROPERTY_', 
          'ListFillRange', 'A1:A3');
   /* Get the contents of the Listbox */
call send(listone, '_GET_PROPERTY_',
          'List', list);

Note: The SAS System does not support passing arrays (or SCL lists) as arguments to an OLE automation server; it supports only receiving the array values returned from the server as a result of the _GET_PROPERTY_ or _COMPUTE_ methods. Also, SAS does not support multidimensional arrays as values supplied to or returned by an OLE automation server. [cautionend]

Using Value Properties

OLE automation servers (including OLE custom controls) can designate one of their properties or methods as a value property, which is used as the default property or method when the automation code accesses an object provided by the server without explicitly specifying a property or method name.

In SCL, you can access the value property of a server by specifying an empty string in place of the property name when invoking _GET_PROPERTY_ or _SET_PROPERTY_, or in place of the method name when using _DO_ or _COMPUTE_. For example, if the Text property is the value property, then the following code:

call notify('sascombo', '_set_property_', '',
            'An excellent choice');

is equivalent to:

call notify ('sascombo', '_set_property_', 
             'Text', 'An excellent choice');

Both the SAS ComboBox and SAS Edit controls (supplied with the SAS System) designate Text as their value property.

Specifying Optional Parameters in OLE Server Methods

Some OLE server applications expose methods that have optional parameters; that is, if you do not specify a value for one or more of the parameters that a method supports, the OLE server uses a default value for those parameters. Refer to the documentation for the OLE server application you are using for information about which parameters are optional.

The SAS System supports the use of optional parameters by letting you specify a SAS missing value in place of the parameter you want to omit. The default missing value character is a period (but that can be changed by using the MISSING system option).

For example, Microsoft Excel supports a ChartWizard method that accepts 11 arguments, most of which are optional. This SCL code invokes this method with all of its arguments:

call send(chart, '_DO_', 'ChartWizard', hcell,   
                 -4098, 6, 1, 0, 0, 1, 
                 "Automation at work!",
                 'Column', 'Value', 'Row');

Here is the equivalent SCL code that omits the optional parameters (substituting the missing value character):

call send(chart, '_DO_', 'ChartWizard', hcell,
                ., ., ., ., ., .,
                "Automation at work!",
                 ., ., .);

Note: Your SCL code must still respect the position of the optional parameters when invoking methods. When you specify a missing value character as an argument, it must be in place of a parameter that is optional to the OLE server's method. [cautionend]

Creating an External OLE Automation Instance

External OLE Automation Instances can be for an application on your local machine or an application on a remote machine. Before you can automate an external OLE application, you must create an instance of the OLE Automation class. (Note that this is not necessary when you automate objects that you embed or link in your FRAME entry, because placing them in the FRAME entry creates the instance for you.) Unlike the OLE class, the OLE Automation class is not derived from the Widget class and, therefore, has no visual component to include in a FRAME entry. Instead, you must load an instance of the HAUTO class (using the LOADCLASS function) in the SCL code that drives the automation. For example:

hostcl=loadclass('sashelp.fsp.hauto');

After you create an instance of the OLE Automation class, you must associate the new instance with an SCL object identifier (which you need to use when calling methods with CALL SEND) and an OLE server application. To obtain the identifier, use the _NEW_ method on the newly created instance of the OLE Automation class. This example stores the object identifier in oleauto and associates the object with Microsoft Excel (which has the identifier Excel.Application.8 in the Windows registry) on the local machine.

call send(hostcl, '_NEW_', oleauto, 0,
          'Excel.Application.8');

To create an instance of the OLE Automation class for a remote machine, the remote machine must be configured to permit the user to start remote instances using Distributed COM Configuration Properties (DCOMCNFG.EXE). In Windows NT, DCOMDNFG.EXE is located in the \WINNT\SYSTEM32 folder. In Windows 95 and Windows 98, DCOMDNFG.EXE is located in the \WIN9x\SYSTEM folder. For more information on Distributed COM Configuration Properties, see your Windows documentation. The following example creates an instance of Microsoft Excel on a remote machine. Once created, the method and property calls to that instance work as if it were on a local machine.

Init:
  HostClass = loadclass('sashelp.fsp.hauto');
  ExcelObj = 0;

  /* Define the machine name and put it in a list  */

  machineName = '\\Aladdin';
  inslist = makelist();
  attrlist = makelist ();

  rc = insertc (attrlist, machineName, -1, 'remoteServer');
  rc = insertl (inslist, attrlist, -1, '_ATTRS_');

  /* Instantiate the Excel object and make it visible */

  call send (HostClass, '_NEW_',ExcelObj, inslist, 
  'Excel.Application');
  call send (ExcelObj, '_SET_PROPERTY_', 'Visible', -1);
return;

For more information about the _NEW_ method, see _NEW_.

After you create an instance of an OLE Automation object, you can automate that object in much the same way you would automate an object that you have embedded or linked in your frame. The following table notes some key differences between the types of objects.

SAS OLE objects... SAS OLE Automation objects...

are derived from the Widget class. are derived from the Object class.

have a visual component (the object you place in the FRAME entry). have no visual component within the FRAME entry.

are created by placing the object in a region in the FRAME entry (using drag and drop). are created by using the LOADCLASS statement and the _NEW_ method in SCL.

represent the specific type of data object (which you choose) supported by the OLE server. represent the top-level application object supported by the OLE server, which you then might use to open objects of specific data types.

allow you to call methods with CALL NOTIFY by passing in the object name from the FRAME entry. require you to call methods with CALL SEND, passing in the object identifier returned by the _NEW_, _GET_PROPERTY_, or _COMPUTE_ methods.

SAS OLE objects...	SAS OLE Automation objects...
are derived from the Widget class.	are derived from the Object class.
have a visual component (the object you place in the FRAME entry).	have no visual component within the FRAME entry.
are created by placing the object in a region in the FRAME entry (using drag and drop).	are created by using the LOADCLASS statement and the _NEW_ method in SCL.
represent the specific type of data object (which you choose) supported by the OLE server.	represent the top-level application object supported by the OLE server, which you then might use to open objects of specific data types.
allow you to call methods with CALL NOTIFY by passing in the object name from the FRAME entry.	require you to call methods with CALL SEND, passing in the object identifier returned by the _NEW_, _GET_PROPERTY_, or _COMPUTE_ methods.

Example: Populating a Microsoft Excel Spreadsheet with SAS Data

SCL Code for Populating a Microsoft Excel Spreadsheet contains SCL code to populate a Microsoft Excel spreadsheet with data from a SAS data set.

SCL Code for Populating a Microsoft Excel Spreadsheet

Load an instance of the OLE Automation class and invoke Excel. Set the object to Visible so you can see the automation in progress.
LAUNCHXL: hostcl = loadclass('sashelp.fsp.hauto'); call send(hostcl, '_NEW_', excelobj, 0, 'Excel.Application'); call send(excelobj, '_SET_PROPERTY_', 'Visible', 'True'); return;

Get the identifier for the current Workbooks property and add a worksheet. Then get the identifier for the new worksheet.
CREATEWS: call send(excelobj, '_GET_PROPERTY_', 'Workbooks', wbsobj); call send(wbsobj, '_DO_', 'Add' ); call send(excelobj, '_GET_PROPERTY_', 'ActiveSheet', wsobj);

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

Traverse the data set and populate the cells of the Excel worksheet with its data, row by row.
do col=1 to nvar; call send(wsobj, '_COMPUTE_', 'Cells',1,col,retcell); var=varname(dsid,col); call send(retcell,'_SET_PROPERTY_', '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, '_SET_PROPERTY_', 'Value' ,var); end; rc=fetch(dsid); end; end; dsid=close(dsid); return;

Close the worksheet and end the Excel session. The _TERM_ method deletes the OLE automation instance.
QUITXL: call send(excelobj,'_GET_PROPERTY_', 'ActiveWorkbook', awbobj); call send(awbobj, '_DO_', 'Close', 'False'); call send(excelobj, '_DO_', 'Quit'); call send(excelobj, '_TERM_'); return;

***SCL Code for Populating a Microsoft Excel Spreadsheet***

Load an instance of the OLE Automation class and invoke Excel. Set the object to Visible so you can see the automation in progress.	LAUNCHXL: hostcl = loadclass('sashelp.fsp.hauto'); call send(hostcl, '_NEW_', excelobj, 0, 'Excel.Application'); call send(excelobj, '_SET_PROPERTY_', 'Visible', 'True'); return;
Get the identifier for the current Workbooks property and add a worksheet. Then get the identifier for the new worksheet.	CREATEWS: call send(excelobj, '_GET_PROPERTY_', 'Workbooks', wbsobj); call send(wbsobj, '_DO_', 'Add' ); call send(excelobj, '_GET_PROPERTY_', 'ActiveSheet', wsobj);
Open a SAS data set.	dsid=open('sasuser.class','i'); call set(dsid); rc=fetch(dsid); nvar=attrn(dsid, 'NVARS'); nobs=attrn(dsid, 'NOBS');
Traverse the data set and populate the cells of the Excel worksheet with its data, row by row.	do col=1 to nvar; call send(wsobj, '_COMPUTE_', 'Cells',1,col,retcell); var=varname(dsid,col); call send(retcell,'_SET_PROPERTY_', '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, '_SET_PROPERTY_', 'Value' ,var); end; rc=fetch(dsid); end; end; dsid=close(dsid); return;
Close the worksheet and end the Excel session. The _TERM_ method deletes the OLE automation instance.	QUITXL: call send(excelobj,'_GET_PROPERTY_', '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, 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.

Visual Basic Code Samples and Their SCL Equivalents shows some excerpts of Visual Basic code and their SCL equivalents.

Visual Basic Code Samples and Their SCL Equivalents
Visual Basic Code OLE Automation in SCL

Launch Excel and make it visible

Set excelobj = CreateObject("Excel.Application") excelobj.Visible = True

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

Create a new worksheet

Dim wbsobj, wsobj As Object Set wbsobj = excelobj.Workbooks wbsobj.Add Set wsobj = excelobj.ActiveSheet

call send(excelobj,'_GET_PROPERTY_', 'Workbooks', wbsobj); call send(wbsobj, '_DO_', 'Add'); call send(excelobj,'_GET_PROPERTY_', '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,'_SET_PROPERTY_', 'Value' ,var);

Close the Excel application object

excelobj.ActiveWorkbook.Close ("False") excelobj.Quit

call send(excelobj,'_GET_PROPERTY_', 'ActiveWorkbook', awbobj); call send(awbobj, '_DO_', 'Close', 'False'); call send(excelobj,'_DO_', 'Quit'); call send(excelobj,'_TERM_');

***Visual Basic Code Samples and Their SCL Equivalents***
Visual Basic Code	OLE Automation in SCL
Launch Excel and make it visible Set excelobj = CreateObject("Excel.Application") excelobj.Visible = True	hostcl = loadclass('sashelp.fsp.hauto'); call send ( hostcl, '_NEW_', excelobj, 0, 'Excel.Application'); call send (excelobj,'_SET_PROPERTY_', 'Visible','True');
Create a new worksheet Dim wbsobj, wsobj As Object Set wbsobj = excelobj.Workbooks wbsobj.Add Set wsobj = excelobj.ActiveSheet	call send(excelobj,'_GET_PROPERTY_', 'Workbooks', wbsobj); call send(wbsobj, '_DO_', 'Add'); call send(excelobj,'_GET_PROPERTY_', '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,'_SET_PROPERTY_', 'Value' ,var);
Close the Excel application object excelobj.ActiveWorkbook.Close ("False") excelobj.Quit	call send(excelobj,'_GET_PROPERTY_', 'ActiveWorkbook', awbobj); call send(awbobj, '_DO_', 'Close', 'False'); call send(excelobj,'_DO_', 'Quit'); call send(excelobj,'_TERM_');

Chapter Contents
Previous
Next
Top of Page