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

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.  

• Accessing Array Values Returned by the OLE Automation Server

• Using Value Properties

• Specifying Optional Parameters in OLE Server Methods

• Creating an External OLE Automation Instance

• Example: Populating a Microsoft Excel Spreadsheet with SAS Data

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

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;

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.

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.  

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.

The following table contains 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');     
call send(excelobj, '_SET_PROPERTY_', 'Visible', 'True');   
return;

CREATEWS:     
call send(excelobj, '_GET_PROPERTY_', 'Workbooks',        
wbsobj);     
call send(wbsobj, '_DO_', 'Add' );     
call send(excelobj, '_GET_PROPERTY_', 'ActiveSheet',        
wsobj);

dsid=open('sasuser.class','i');
  
call set(dsid);   
rc=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,'_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;

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.

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');

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 );

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);

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

Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.