Chapter 10—Automation

Automation, formerly called OLE Automation, is a technology that allows you to utilize an existing program’s content and functionality, and to incorporate it into your own applications. Automation is based on the Component Object Model (COM). COM is a standard software architecture, based on interfaces, that is designed to separate code into self-contained objects, or components. Each component exposes a set of interfaces through which all communication to the component is handled.

For example, with Automation you can use a Word processor’s mail merge feature to generate form letters from data in an Omnis database without the user being aware that Word processor is involved.

Automation consists of a client and a server. The automation client (Omnis) attaches to the automation server so that it can use the content and functionality that the automation server provides. Omnis can only be an automation client, it cannot be a server.

Automation servers consist of an object or a number of objects. For example, in the object hierarchy for Excel, the uppermost object in the Excel object model is the Application object. The Excel Application object has many children, two of which are Workbooks and CommandBars. Workbooks and CommandBars are collection objects that contain other objects. A Workbooks collection object contains Workbook objects and a CommandBars collection objects contain CommandBar objects. And, the list goes on, but understanding the object relationships in the automation server is fundamental to its use in Omnis and the third-party manufacturer can only provide the appropriate level of documentation for these relationships. See the Excel documentation for an illustration of the object hierarchy.

OLE2 Menu Options

Note that from Studio 11, the OLE2 menu options Links, Object and Insert Object have been removed from the Edit menu under Windows (these were used by OOLE2 which was removed in Studio 6.1).

Instantiating an Automation Server

In Omnis, before you can utilize automation servers, you need to get an object (in Excels’ case you need to obtain an Application object).

You can obtain an automation object by:

• Creating an object variable with subtype set to the automation server that you require. Once created, you will have to invoke a standard method, such as $createobject(), to instantiate the server.

• Calling the OLE picture control $getobject() method and using the returned object variable.

• Querying an ActiveX controls’ property, or calling a method, which returns an object variable.

Automation Server Functionality

An object by itself does nothing unless you can do something with that object. To programmatically examine or control an object, you can use the properties and methods that the object supports. A property is a function that sets or retrieves an attribute for an object. A method is a function that performs some action on an object.

For example, in Omnis, you can navigate to an object by starting at the uppermost object and working your way down to your target. Consider Excel and its Workbooks collection object, which represents all the open workbooks. You could use its Count property to acquire the count of workbooks open in Excel:

lNum = objectApplication.$Workbooks().$xcount

You may notice that the above example references a property called xcount whereas the Microsoft Excel automation documentation refers to the property as count. This is because, Omnis already has $count as a core internal notation attribute and has therefore appended “x” as a prefix. Unfortunately, other automation properties and methods also clash with Omnis internal notation but have not been renamed. To utilise the automation attributes simply add two colons before the name. So $xcount becomes $::xcount. Adding two colons even when they aren’t required will not have an effect, so it is a good habit to get into.

Another example would be to enquire on a particular value in a worksheet:

CValue = objectApplication.$Workbooks(“Book1.xls”).$Worksheets(“Sheet1”).Range(“A1”).Value

If you require a particular object often, it is beneficial to assign the automation object to a Omnis object variable. To do this simply, create an object variable, with an empty subtype, and then invoke the automation method/property and assign the results. For example:

Do objectApplication.$Workbooks("Book1.xls").$Worksheets("Sheet1") returns objWorkSheet
Do objWorkSheet.$Range("A1").$value.$assign("Cell Value")  

So, how exactly, do you know what automation objects have what properties and methods?

• Obviously the best source of documentation is the third-party, which developed the automation server. For example, for Microsoft Office software, you can refer to the vbaxl8 (Excel), vbagrp8 (Graph), vbaoff8 (Office), vbaoutl (Outlook), vbappt (Powerpoint), vbawrd8 (Word), help files.

• Use an automation object browser such as the OLE/COM Object viewer, which is included with Microsoft Studio.

• Use the Omnis Interface manager and Values list.  

• And lastly, many Microsoft Applications come with a Macro recorder, which enables you to record user actions as a Visual Basic for Applications, or VBA, script.

To illustrate this, follow these simple steps: -

• Start Microsoft Word.

• On the Tools menu, click Macro, and then select Record New Macro. In the Store Macro In drop-down box, select the name of the active document. Make note of the new macro’s name, and then click OK to start recording.

• Start a new document.

• Type one and press ENTER.

• Type two and press ENTER.

• Type three.

• On the File menu, click Save, and save the document as C:\doc1.doc

• Click the Stop Recording button (or, on the Tools menu, click Macro and then Stop Recording).

To view the VBA code that the macro recorder generated from your actions, on the Tools menu, click Macro, and then click Macros. Select the name of the new macro in the list and click Edit. The Visual Basic Editor displays the recorded macro.

Documents.Add
Selection.TypeText Text:="one"
Selection.TypeParagraph
Selection.TypeText Text:="two"
Selection.TypeParagraph
Selection.TypeText Text:="three"
ActiveDocument.SaveAs FileName:="Doc1.doc",
  FileFormat:=wdFormatDocument, _
  LockComments:=False, Password:="", AddToRecentFiles:=True,
  WritePassword:="", ReadOnlyRecommended:=False,
  EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False,
  SaveFormsData:=False, SaveAsAOCELetter:= False

Whilst, this isn’t valid Omnis script code, it does illustrate the automation properties and methods, and usually it is simply a case of making a few minor modifications such as adding the $ prefix to the names and appending the parenteses to methods.

Built-in Methods

Along with the automation server’s methods, Omnis provides additional methods to enable you to manage the server.

These methods are: -

• $createobject() creates an instance of the object and may be called before the automation server can be used.

• $getactiveobject() obtains an instance to a current automation object.

• $getobject(Filename,[class]) creates an instance of an object from the specified filename and class, if supplied. For example $getobject("C:\CAD\SCHEMA.CAD")

• $isavailable() returns kTrue if the object variable has a server instance, or kFalse otherwise.

Whether these methods exist or not, will depend on the source of the object variable. For example, an object, which originated from an ActiveX, OLE2, or an automation collection, will not have these methods.

Lifetime of an Automation Server Instance

The lifetime of an automation instance typically follows these steps:

• Create an instance of the object using $createobject, $getactiveobject or $getobject. And validate that an instance was created by calling $isavailable.

• Communicate with the object via properties and methods.

• Finally, terminate the object. Many automation servers provide a method called $quit which should be used to ensure that the instance is terminated.

Terminating Processes

The $quit() method of an Automation Object has an optional parameter 'hwnd' to allow you to terminate the object’s process. For example:

Do ExcelObj.$quit(ExcelObj.$hwnd)

will terminate the automation object's process once the QUIT method finishes.

Automation Event Handling

Some automation servers fire events, for example, an email application may fire an email alert event to signify new emails. In Omnis, it is possible to intercept COM automation events (ActiveX and OLE2 events are handled differently). To receive automation server events, you need to add the "enableEvents" item to the "ole2auto" section of the config.json file and set it to true:

"ole2auto": {
  "enableEvents": true
},

To intercept events, you simply sub-class the automation server object and override the desired events. The event method name will have the suffix ‘event’ added.

For example, the following steps illustrate the trapping of events for an automation server called UrlReader, which is available from Microsoft.

• Before you start Omnis, ensure that you have used regsvr32 (a DOS program which is distributed by Microsoft and used to register automation servers) to register the UrlReader server.

• Create a new object, which is subclassed from the automation object ‘UrlReader.UrlReader.1’. To do this either, use the Class Wizard ‘New Sub-class object’ from the Studio Browser and select “Automation>>UrlReader.UrlReader.1” from the list or create an object and change the superclass property to “Automation>>UrlReader.UrlReader.1” using the superclass dialog.

• Now open the method editor for the new object. You will notice numerous methods for the UrlReader object. Two of which will have the suffix ‘event’. Automation events have the extension “event” appended by Omnis. Now override both event methods and add the code ‘Send to tracelog Event triggered’, then close the method editor.

• Create a new window and add a button.

• Open the method editor for the window and add an object variable called myObj with the subtype that you named your object (in the first stage).

• Add the following code:

$construct:      Do myObj.$createobject()
$destruct:       Do myObj.$quit()
Button evClick  Do myObj.$readurl(http://www.microsoft.com/,"c:\mscom.txt")

• Open the window and the tracelog. Then press the button. Notice that the tracelog will contain “Event triggered” text.

Automation to Omnis Variable Conversion

An advanced topic is the differences between variable types in automation and in Omnis. This topic becomes less and less relevant as the automation servers become more and more flexible; unfortunately, some of the older automation servers are rather inflexible when it comes to the type of variables used.

Automation has a parameter type called VARIANT; this type can hold any type of data. Unfortunately with this flexibility comes a price. Namely that some objects, Excel for example, state that they handle any type of data (ie VARIANT) in theory, but in fact they may be expecting data passed by reference or of a limit subset of the VARIANT types.

The Omnis automation component takes the same approach as Visual Basic, in that everything is passed by VARIANT, and depending on the Omnis data type used, certain assumptions are made.

All these assumptions can be over-ridden; the table below shows the default conversion.

Typically a server will return the error code of 80070057 (see Automation errors section) if the parameter wasn’t of the correct type. You can coerce variables to another datatype, by preceding the parameter with a constant listed in the table below:

For example:

Do object.$setvalue(kAutoI4,"45")

Automation Errors and Limitations

Should an automation error occur, then the contents of #ERRCODE and #ERRTEXT can be used to isolate the problem.

On an error, #ERRCODE will be set to the automation error code type, HRESULT, which is a 32bit unsigned integer. A value of –1 indicates that the error occurred in the automation component rather than in the automation server, for example, “Automation method not found”.

#ERRTEXT will contain a string representation of the error.

HRESULT codes are difficult to document as they can be defined by both the server application and by the operating system. However, here are a few of the more common codes (which are in hexidecimal):

An automation limitation is Constants, or automation enums, which are not supported for COM objects and OLE2 objects.

Automation Examples

Some of the best examples of automation in Omnis are contained within the automation sample library, but the following illustrate the use of XML, DAO and Outlook in Omnis.

XML

This example requires the file books.xml, which is available from Microsoft. Ensure that you obtain the correct version, which have “AUTHOR” tags, in the correct case; otherwise you will have to subsitute the “AUTHOR” tag with another tag in the example code.

The variables xml (type Object and subtype ‘Microsoft.XMLDOM.1.0’) and element (type Object, no subtype) need to be added to your code.

Do xml.$createobject()
Do xml.$async.$assign(kFalse)
Do xml.$load("c:\books.xml")
Do xml.$getelementsbytagname("AUTHOR") Returns element
For #1 from 0 to element.$length-1 step 1
  Calculate #S1 as element.$item(#1).$xml
  OK message {[#S1]}
End For 
Do xml.$quit()

This example loads the xml file and enumerates each AUTHOR tag.

DAO

This example requires the Microsoft Office sample database northwind.mdb. The variables obj (type Object and subtype ‘DAO.DBEngine.36’) and database, recordset, both of type Object (no subtype).

Do obj.$createobject()
Do obj.$Workspaces(0).$opendatabase("c:\office\northwind.mdb") Returns dat
Do dat.$openrecordset("Select * from Products",4) Returns recordset
Calculate #1 as recordset.$fields().$xcount
For #2 from 1 to #1 step 1
  Calculate #S1 as recordset.$fields(#2-1).$::value
  Calculate #S2 as recordset.$fields(#2-1).$::name
  Send to trace log {[#S2]=[#S1]}
End For 
Do recordset.$close()
Do dat.$close()
Do obj.$quit()

This example opens the database file and enumerates each product in the record set.

Outlook 2000

This examples requires variables ol (type Object and subtype ‘Outlook.Application.9’) and variables olns, objFolder, ocontacts, and ocontact (all of type Object and no subtype).

Do ol.$createobject()
Do ol.$getnamespace("MAPI") Returns olns
Do olns.$getdefaultfolder(10) Returns objFolder
Do objFolder.$items() Returns ocontacts
OK message {There are [ocontacts.$xcount] contacts}
Do ocontacts.$getfirst() Returns ocontact
For #1 from 1 to ocontacts.$xcount step 1
  Calculate #S1 as ocontact.$xfullname
  OK message {[#S1]}
  Do ocontacts.$getnext Returns ocontact
End For 

This example enumerates the contacts in your Outlook application.