Previous Page | Next Page

Using OLE in SAS/AF Software under Windows

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:

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 methods, such as the methods listed in this table.)  [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

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. The arguments contain actual static values, not pointers to values that you can modify.  [cautionend]


Accessing Array Values Returned by the OLE Automation Server

Using SCL methods and the OLE automation server, SAS lets you

receive a single-dimensional array that is passed to SAS as an SCL list

send or receive multi-dimensional SCL arrays.

In this first example, the SCL code creates and populates a list box in a Microsoft Excel worksheet and stores the contents of the list box 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);

Using several SCL arrays, the following SCL code creates and populates another Microsoft Excel worksheet:

Init:
/* Initialization */
   HostClass = loadclass('sashelp.fsp.hauto');

/* Instantiate the Excel object and make it visible */
   call send (Hostclass, '_NEW_', ExcelObj, 0, 'Excel.Application');
   call send (ExcelObj, '_SET_PROPERTY_', 'Visible', -1);

/* Get the Workbook Object, add a new Sheet and get the Sheet object */
   call send (ExcelObj, '_GET_PROPERTY_', 'Workbooks', WkBkObj);
   call send (WkBkObj, '_DO_', 'Add');
   call send (ExcelObj, '_GET_PROPERTY_', 'ActiveSheet', WkShtObj);

   dcl char names{3,2) =  ('Lucy', 'Ricky',
                          'Julliette', 'Romeo',
                          'Elizabeth', 'Richard');

/* Set the range to be A1:A4 and fill that range with names */
   call send(WkShtObj, '_COMPUTE_', 'Range', 'A1', 'B3', RangeObj);
   call send(RangeObj, '_SET_PROPERTY_', 'Value', names);

   dcl num primes{2,4} = ( 1, 3, 5, 7, 
                          11, 13, 17, 23);

/* Set the range to be A5:D6 and fill that range with ints values */
   call send(WkShtObj, '_COMPUTE_', 'Range', 'A5', 'D6', RangeObj);
   call send(RangeObj, '_SET_PROPERTY_', 'Value', primes);

   dcl char totals{1,4} = ('=SUM(A5,A6)', 
                           '=SUM(B5,B6)',  
                           '=SUM(C5,C6)', 
                           '=SUM(D5,D6)');

/* Set the range to be A1:A4 and fill that range with totals */
   call send(WkShtObj, '_COMPUTE_', 'Range', 'A7', 'D7', RangeObj);
   call send(RangeObj, '_SET_PROPERTY_', 'Value', totals);

   dcl char vals{7,4};

   call send(WkShtObj, '_COMPUTE_', 'Range', 'A5', 'D7', RangeObj);
   call send(RangeObj, '_GET_PROPERTY_', 'Value', vals);
return;


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 SAS) designate Text as their value property.


Specifying Optional Parameters in OLE Server Methods

Some OLE server applications expose methods that have optional parameters. 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.

SAS 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. (This action 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 in the Windows registry) on the local machine.

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

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). The DCOMCNFG.EXE is located in the \WINNT\SYSTEM32 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.


Example: Populating a Microsoft Excel Spreadsheet with SAS Data

The following table contains SCL code to populate a Microsoft Excel spreadsheet with data from a SAS data set.

SCL Code for Populating a Microsoft Excel Spreadsheet
Action SCL Code
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 do;
            varn=getvarn(dsid,col);
            call send(retcell, '_SET_PROPERTY_', 'Value' ,varn);
         end;
         else do;
            varc=getvarc(dsid,col);
            call send(retcell, '_SET_PROPERTY_', 'Value' ,varc);
         end;
      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.

The following table 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_');

Previous Page | Next Page | Top of Page