Wordperfect Quattro Pro

Reference: Using OLE automation

OLE automation is an object-based standard that gives one application (an OLE automation server) the ability to provide objects to other applications or development tools (OLE automation controllers). Quattro Pro can function as either an automation controller or an automation server.

When using Quattro Pro as an automation controller, the OLE automation feature lets you create and manipulate objects in other applications that support OLE 2.0 automation as a container. Several Quattro Pro macros can help application developers initiate and conduct DDE exchanges, called conversations.

OLE automation lets you operate another application from within Quattro Pro. For this to happen, an object model must be created for the automated application. In the case of a television, objects might represent the volume control, the power control, the channel selector, and various tuning controls for color quality and image centering. Properties are the settings used to control object appearance and other attributes, while methods are the actions you can perform with or on each object.

When using Quattro Pro as an automation server, the OLE automation feature lets you expose Quattro Pro’s programmable objects to an OLE automation controller. For example, other applications, such as WordPerfect, and development tools, such as Microsoft Visual Basic, can utilize the functionality of Quattro Pro to create items such as charts and reports.

OLE automation works through automation expressions created with macro statements. The process to follow for working with OLE automation and macros involves opening an application and creating a new object or getting an existing object, using macro commands and an application’s automation methods to set properties and perform other actions, and closing the object and exiting the application.

Example of setting up Notes/FX field blocks

The Notes/FX feature uses a field block to determine how Lotus Notes exchanges data. The field block is defined in two columns of information or database cells. The first column lists the field names, which exchanges data with similarly named fields in Lotus Notes. The second column contains a field value or block reference.

This is an example of a field block.

The labels in A6..A9 are field names; B6..B9 contains field values.

There are three types of fields: data fields, predefined fields, and reference fields.

Data fields

Data fields contain data from a Lotus Notes database. You can edit data fields in Lotus Notes. The data is always stored in Lotus Notes. In the example above, Author and Date are data fields. The following table illustrates the bidirectional fields used to share data between Quattro Pro and Lotus Notes:

Predefined fields

Predefined fields contain a calculated value, such as the size of a notebook or its creation date. In the example shown above, SizeInK is a predefined field. Here are the predefined fields supported by Quattro Pro with the related Notes fields and data types:

Reference fields

Reference fields are block coordinates that reference a block of data. In the above example, DataBlock is a reference field. You also reference this block of cells when naming the field block.

Automation terms

These terms are important for understanding OLE automation features. The terms are listed from the most basic to the most complex. For the best understanding, read their definitions in order.

Automation objects

An automation object represents all or part of an application that has been exposed for OLE automation. Most applications that support OLE automation as clients expose the main application object.

Automation objects have properties and methods that can be used to change the attributes of objects and act on them. To learn which objects are exposed for OLE automation, check the documentation accompanying the application.

Methods and collections

Methods are associated with automation objects. Methods act on objects and usually return the result of the action. To learn which methods are available for each object, check the documentation for the application.

A collection is a group of related automation objects, usually listed as a plural. For example, if MenuItem is an object, MenuItems could be a collection of all menu items. Like objects, collections can have their own sets of properties and methods.

Properties

Properties are characteristics of an automation object that describe its current state, usually appearance or behavior. You can use methods to change some property settings. Other properties can be changed directly by specifying a different argument.

Values

In OLE automation, a value can be a number, a double-quoted string, an automation object, or empty. Values can be stored as variables and included in expressions.

Variables

In OLE automation, a variable is a place to store a value. A variable can be a cell (used to store a string, a number, or nothing), a member of an automation object, or a named variable (used to store values of any type, often automation objects, in contrast with cells, which can hold only strings and numbers).

Named variables

Named variables are automation expressions created with the {ASSIGN} macro. They cannot be used in formulas directly, but they can be used indirectly if you first assign them to a cell name or reference. Named variables last as long as Quattro Pro is running. To delete one before closing Quattro Pro, use the {DELVAR} macro.

When an expression is parsed, names are first assumed to identify a cell, cells, and then named variables. This means that a named variable cannot have the same name as a cell reference, a group of cells, or a cell name in the notebook that contains the currently executed macro. Otherwise, it is not accessible.

The name of a named variable cannot contain any formula operators or leading or trailing spaces, although spaces within the name are acceptable.

Expressions

An expression symbolizes a value or variable, depending on its use. An expression can be a string, a number, a cell (cell reference, cell coordinates, or cell name), the name of a named variable, a formula expression, or an automation expression.

If an expression is used to express a value and it represents a variable, the value of the variable is returned. For example, if a value is expected from expression A1, the value stored in A1 is used.

If an expression is used to express a variable and it represents a value, the value is converted to a cell or named variable if the value is a string. For example, +”A”&”1″ results in a cell variable representing the cell reference A1.

Arguments

An argument is information supplied to part of an automation expression, or to a property, method, or formula.

Automation expressions

An automation expression is an automation object (or object hierarchy) plus any associated property or method and arguments, if any.

In its simplest form, the syntax is object.property or object.method. Because automation objects are usually found in hierarchies, members of successive hierarchical levels can be used to specify the exact target object. Elements of automation expressions are separated by periods.

OLE automation syntax

Automation expressions introduce OLE automation expressions and their basic syntax. The full syntax is one of the following:

starting_object

starting_object.member_cascade

where:

All OLE automation methods and properties can be included as a member, usually at the end of an expression. A property or method without arguments can be used without parentheses.

The object connector, shown above as a period (.), is determined by the international setting for a decimal point. The list separator, shown above as a comma (, ) is determined by the international separator character setting.

OLE automation object functions

Quattro P
ro uses two OLE automation object functions, GetObject and CreateObject, to create and retrieve objects from other applications and insert them in notebooks. These functions perform different tasks depending on the arguments used with them and the application that provides the objects. Both functions accept the progID argument, which is a programmatic identifier registered by an application during its installation (for example, Excel.Application or Visio.Application). These object functions can be used in OLE automation expressions within Quattro Pro macros.

CreateObject

CreateObject(progID) locates the target application, loads it, creates an object, and returns the object. The application can be loaded only if its path is registered or specified in PATH. For example, CreateObject(“Visio.Application”) returns a Visio graphics object.

Each OLE application is registered with a unique name (CLSID) in the registry when it is installed. The registry location is HKEY_Classes_Root/application name/CLSID/curver. This name is used within CreateObject.

This object function is not the same as the macro {CREATEOBJECT}.

GetObject

This object function retrieves an existing object from somewhere, depending on the given arguments:

OLE automation and macros

These macro commands directly support OLE automation in Quattro Pro:

Because macro command arguments are value expressions, you can pass an automation expression as an argument to any macro command. For example, if calc.accum represents the accum value of the calc named variable, and excel.ActiveCell.Value represents the current value of the Microsoft Excel ActiveCell object, then both these macros are valid:

  {DOWN calc.accum}   {DOWN excel.ActiveCell.Value}   

Quattro Pro does not support the use of automation expressions in formula expressions. So the following macros are invalid:

  {IF named_variable>0} {do something}   {LET A2, named_variable+A1}   {IF named_variable.member>0}   

To solve this problem, first assign the value of an automation expression to a cell, and then use that cell in a formula expression:

  {ASSIGN A1, named_variable}   {IF A1>0}{do something}   

OLE automation expression examples

OLE automation expressions are used within Quattro Pro macros to perform OLE automation tasks. Automation expression syntax defines acceptable syntax for OLE automation expressions. The following are acceptable expression forms. Note that progID is the program identifier registered for an application during its installation.

  named_variable   CreateObject(progID)   GetObject(filename and/or progID)   named_variable.member   named_variable.member1.member2(arg1, arg2)   named_variable.member1(arg1, arg2).member2   

OLE automation macro examples

The automation expressions execute in the background of the application. For example, the following macro opens the Microsoft Excel file, test.xls, and saves it as test2.xls:

  {ASSIGN Test, GetObject("c:\excel\test.xls!C2:C6")}   {EXECAUTO Test.SaveAs("c:\excel\test2.xls")}   {ASSIGN File, GetObject("c:\excel\test2.xls")}   {ASSIGN Myrange, File.Range("Myrange")}   {IFAUTOOBJ Myrange}{LET G39, "New"}{RETURN}   {LET G39, "Empty"}   

If you try to assign another variable to test2.xls without using {DELVAR} to delete the variable File, which represents test2.xls, you are prompted that test2.xls already exists and is open. The following macro opens the graphics application Visio, creates a new file, draws a rectangle in the workspace, saves the file, and then exits Visio:

  {ASSIGN VisioApp, CreateObject("Visio.Application")}   {IFAUTOOBJ VisioApp}{EXECAUTO VisioApp.Documents.Add("")}   {EXECAUTO VisioApp.ActivePage.DrawRectangle(1, 7, 5, 10)}   {EXECAUTO VisioApp.ActiveDocument.SaveAs(TestFile)}   {EXECAUTO VisioApp.Quit()}   

TestFile is the cell name of a cell containing this string: C:\DATA\OLE2\VISIAUTO.VSD. The macro statement that saves the active document as TestFile actually creates visiauto.vsd in the specified directory. The next macro names a variable Test, assigns zero to it, prompts for an entry other than zero, and then stores it in Test:

  {ASSIGN Test, 0}   {GETNUMBER "Enter a number other than 0:", Test}   {ASSIGN VariableValue, Test}   {DELVAR Test}   {IF VariableValue<>0}{LET A1, "PASS"}{RETURN}   {LET A1, "FAIL"}   

After assigning the value in Test to VariableValue (the name of a cell), and deleting Test, it evaluates VariableValue and determines whether Test received a new value that was assigned to VariableValue.

Macro syntax and arguments

Macro commands, like functions, have specific grammatical rules, or syntax. The syntax for macros is:

{COMMANDNAME Argument1, Argument2, Argument3…}

COMMANDNAME is the exact name of the command. Arguments are values providing instructions to the command. Not all macro commands require arguments, but when they do, they require a specific type of information. The following examples are in Quattro Pro script form. To use them in PerfectScript, modify these in the PerfectScript code accordingly. For information about converting Quattro Pro script to PerfectScript, see “Choosing a tool for creating macros.” Some examples of macro commands are:

  {Query.Criteria_Table B27..B29}   {BlockCopy A1, A2..A37}   {Search.Find "3rd Quarter Profits"}   {BEEP 3}   {GETNUMBER "How old are you?", AGE}   {CONTENTS E15, F15, 15}   

Many command equivalents contain a period (.) in their command name. The syntax rules for Quattro Pro macro commands are as follows:

•

Separate multiple arguments with commas. To use semicolons or periods as separators instead, change the setting in Tools Settings International Punctuation.

Because macro commands are labels, a syntax error is not recognized when you mistype a macro command; an error occurs instead when you try to play (or run) the macro. To save debugging time, pay careful attention to the format of macro commands as you enter them, and record macros whenever possible.

Macro command arguments

Arguments in macro commands require specific information to be supplied with the command. There are four types of arguments: numbers, strings, locations, and conditions.

Number arguments

Number arguments require any numeric value, entered as

String arguments

String arguments require a text string, entered as:

Location arguments

Location arguments require a reference to a cell or cells. The reference can be

Condition arguments

Condition arguments require a logical expression. A logical expression is a formula that can be evaluated as either true or false; for example, +C4 > 500.

Some commands accept a combination or choice of argument types; for example, {LET} stores either a label or a number in a cell, depending on the argument type.

Macro subroutines

A subroutine is a macro stored separately from macros that use it. You name the subroutine the same way macros are named. You can call the subroutine from another macro, and Quattro Pro plays the subroutine’s macro commands. After playing the subroutine, the command immediately following the call (in the main macro) plays. Using subroutines makes the macro more readable and easier to debug.

To call a subroutine from within a macro, type its name inside braces. For example, {go_right} calls the subroutine named go_right.

You can pass arguments to a subroutine for use by its commands. These arguments are stored in cells referenced by the subroutine. For example, {set_cost C10, 36} calls the subroutine set_cost and passes two arguments (C10 and 36) to it.

For a subroutine to know what to do with the arguments, you must define them within the subroutine by using {DEFINE}. This macro command tells Quattro Pro where to store the arguments and whether they should be interpreted as values or labels.

Whenever you call a subroutine, Quattro Pro stores the return point in an internal list called a stack. One return point in this stack clears when the subroutine encounters a {RETURN} command or empty cell. If you do not clear all of these locations, the stack fills up, causing the error “Too Many Nesting Calls”.

The {BRANCH} command can move to or return from any cell in a macro. Use {BRANCH} instead of a subroutine when a macro

Command equivalents

Command equivalents perform operations usually done with menus or dialog boxes. Options usually set in a dialog box are passed as arguments to a command equivalent, in the same manner as passing them to a subroutine. Command equivalents make macros easier to read and understand and let the macro play in any Quattro Pro menu system.

Menu choices with many settings (such as File Print) have a set of command equivalents that emulate their operation. The following example shows a macro that emulates Edit Find and replace:

  search_string March   \a {GETLABEL "Search For? ", search_string}   {Search.Block A:A16..D26}   {Search.Find +search_string}   {Search.ReplaceBy April}   {Search.Direction Row}   {Search.Match Part}   {Search.Case Any}   {Search.Replace}   \y {Search.Next}   

Notice that each command equivalent sets one option in the Find and replace dialog box. You do not have to set each option every time. If one of the command-equivalents is omitted, the default setting is used. The final command ({Search.Next}) performs the Find operation. You can use command equivalent names with COMMAND to find current settings. For example, @COMMAND(“BlockFill.Series”) returns the current setting of Series in Edit Fill Fill series.

Menu choices with a few settings have one command equivalent that emulates their operation. Command equivalents without a period are typically of this type.

When you specify a command equivalent with a question mark (?) after the command name, the macro command displays a dialog box that the user can manipulate. If the name of the command equivalent contains a period, use only the part of the command name that precedes the period. For example, {BlockFill?} displays the Edit Fill Fill series dialog box for the user to manipulate. When the user clicks OK, the fill occurs and the macro resumes.

When you specify a command equivalent with an exclamation mark (!) after the command name, the macro command displays a dialog box that the macro can manipulate. You can make the dialog box revert to user control at any point in the macro using the command {PAUSEMACRO}.

DDE macro commands

Dynamic Data Exchange (DDE) lets Windows-based applications communicate with one a
nother. Use {INITIATE} to open a channel of communication with another application. This is called initiating a conversation. Every DDE conversation consists of a client and a server. The application initiating the conversation is the client; the other application is the server. When you initiate a conversation, you can use {POKE} to send data, {REQUEST} to receive data, or {EXECUTE} to play macros in the server, {TERMINATE} ends the conversation.

The data source that a DDE conversation connects with in the server application is called a topic. Many DDE applications support the topic system and items available from it.

Other DDE applications can call Quattro Pro as their server. To do so, use the server name “QPW” and specify “System” or the filename of an open notebook as the topic. When a notebook is the topic, you can enter cell addresses or coordinates as the item to request, or you can play macro commands. When System is the topic, you can play Quattro Pro macros or request the items listed in the following table:

To play macro commands in Quattro Pro using DDE, establish a link to Quattro Pro using “QPW” as the DDE topic. To play PerfectScript macro commands in Quattro Pro using DDE, establish a link using “QPMacros” as the DDE topic. Then use the {EXECUTE} command provided by the DDE client application to play the command.

Object macro commands

Object macro commands can create Quattro Pro objects, change their property settings, or move them to new positions. Object commands can create drawn objects, dialog controls, and floating objects.

Drawn objects

Drawn objects are objects in a graphics window that you usually create using the toolbar. When creating a drawn object with a macro command, specify the position for the object by stating how far (how many pixels) it should appear from the upper-left corner of the chart background. (A pixel is the smallest dot that Windows can display on your screen.)

You can create drawn objects with {CREATEOBJECT}. The following command activates a graphics window and creates a line near the upper-left corner of the chart:

{GraphEdit "PROFITS"}

{CREATEOBJECT “Line”, 0, 0, 25, 25}

Dialog controls

Dialog controls are objects in a dialog window. When creating a dialog control using a macro command, specify the position by stating how far (how many pixels) it should appear from the upper-left corner of the dialog window.

You can create dialog controls with {CREATEOBJECT}. For example, the following command creates a push button in the active dialog window:

{CREATEOBJECT "Button", 43, 41, 58, 77}

Floating objects

Floating objects are form control buttons and charts that display on the spreadsheet. Unlike chart objects and dialog controls, the position of a floating object is specified as an offset from a cell in the notebook. The offset is specified in twips; each twip is 1/1440th of an inch.

You can create floating objects with {FLOATCREATE}. For example, the following macro command creates a button that is half an inch from the left edge of A:A1:

{FLOATCREATE "Button", A:A1, 720, 0, A:B2, 720, 360, “Bt1″}

In this example, A:A1, 720, 0 specifies that the upper-left corner of the button is in A:A1, is 720 twips (half an inch) from the left side of the cell, and is zero twips from the top of the cell. A:B2, 720, 360 specifies that the lower-left corner of the button is in A:B2, 720 twips from the left side of A:B2, and a quarter inch (360 twips) from the top of A:B2.

After you create an object with a macro command, use {SETPROPERTY} to set its name or {GETPROPERTY} to store its name in a cell. Then you can change its properties with macro commands at any time.

Selecting, positioning, and sizing objects

Dialog controls and floating objects are selected after you create them, so you can reposition them or change their property settings. There are three commands that select Quattro Pro objects:

Changing or reading property settings

You can use {SETPROPERTY} and {SETOBJECTPROPERTY} to change the property settings of Quattro Pro objects. For
example, the following macro selects cells and changes their text color:

{SELECTBLOCK A:A1..C22}

{SETPROPERTY “Text_Color”, 5}

{SETPROPERTY Text_Color, “6″}

You can also change a property setting without selecting the cells using the following command:

{SETOBJECTPROPERTY "A:A1..C22.Text_Color”, 5}

You can use {GETPROPERTY} and {GETOBJECTPROPERTY} to read property settings. {GETPROPERTY} reads settings of the selected object; {GETOBJECTPROPERTY} lets you read property settings without selecting an object.

Reference: Using OLE automation