Advanced OLE Automation
Andrew Ross MacNeill
Melton Technologies Inc.
This session is not an introduction to OLE; rather, it is geared towards using OLE Automation to further application development. OLE Automation can be used for more than passing calculations through Excel or writing Word documents; OLE Automation can completely redefine the types of applications developers build and the way they build them.
These session will focus on a wide variety of OLE Automation controls as well as two powerful tools, Microsoft Project and Visio as examples of advanced OLE Automation. Samples shown in the session will include building class diagrams with Visio and project plans with Microsoft Project. Also discussed are the different ways integration can work: hidden, where the automation server lies hidden on the users desktop or ; visual where the OLE object has a graphical entity that the user can "look & feel".
Visual FoxPro Commands
Before jumping right into OLE Automation, lets quickly review the commands used in creating objects in Visual FoxPro.
CREATEOBJECT / ADDOBJECT
These two commands are used in everyday Visual FoxPro development. CREATEOBJECT creates an object, returning a pointer to the new object. ADDOBJECT adds an object to its parent as x.ADDOBJECT("Container","Container).
CREATEOBJECT may or may not make the object created visible. When working with OLE, this depends on the server itself. For example, the code
x = CREATEOBJECT("Word.Document")
starts Word for Windows and opens a document. Conversely, the code
x = CREATEOBJECT("Excel.Application")
creates an Excel object but doesnt start Excel.
When using OLE Automation, the developer is required to know about the OLE Server. As a result, the main target for OLE Automation should not be the office that only uses FoxPro but rather the office that uses a wide variety of tools.
Information about each OLE Server is contained in the Windows Registry.
FoxPros GENERAL Fields can be used as containers for OLE Objects. This lets you keep all of the information within Visual FoxPro tables.
GETOBJECT provides the developer with a link into an existing document or file. If information is being stored externally or the OLE Application you are developing requires links to existing files, GETOBJECT can be used to retrieve a pointer to an applications document.
X = GETOBJECT("C:\DRAWINGS\CLASS.VSD")
will open a pointer to the Visio drawing so that it can be controlled.
You can also specify a second parameter to the GETOBJECT to specify the type of OLE Server you want to use when opening the file.
Important to note is that AMEMBERS( ) doesnt always behave properly when working with OLE Servers. Using the above example, X is a Visio Document. The following code, however doesnt return what we really need:
? AMEMBERS(la, X, 1)
In normal FoxPro, we would expect that any properties that were also arrays would be returned. This isnt the case with the Visio OLE Server. There are only two properties in a Visio Document class. The rest of the information in the object is stored in Collections which will be discussed further below.
SYS(3004), SYS(3005), SYS(3006)
These System functions change OLE Servers for localized use and are especially critical in International applications. While Visual FoxPros classes do not get automatically localized when you run a localize version (ie THIS.Execute stays THIS.Execute), some OLE Servers expect that the commands they receive are based on the localized version of the client. What this means is that if you are running the German version of FoxPro and you create an Excel object, it expects the OLE Commands to be in German.
What assumptions can be made here? Most localized Microsoft products support the language of the localization as well as English. If you are building an international product, set the Localization to English in order to ensure consistency in International useage.
SYS(3004) returns the current Visual FoxPro Locale ID (LCID), which is determined by the Visual FoxPro Language ID (LangID) and Sort ID.
The Locale ID determines the language in which OLE automation and OLE controls exchange information. The default Visual FoxPro Locale ID is 1033, English.
The SYS(3005) function allows you to specify the localization being used.
Tells any OLE Server that commands coming from Visual FoxPro will be in US format.
Tells the OLE Server that commands from Visual FoxPro will be in German.
SYS(3006) sets the Language Id used by Visual FoxPro.
Automation vs. Controls
As developers begin to move further into Visual FoxPro, the line drawn between OLE Automation and OLE Controls begins to blur. In FoxPro 2.x, the difference was straight forward - automation was calling another program to perform a particular task; controls were the ability to visually use the object from within FoxPro. In Visual FoxPro, developers should not limit their understanding to that distinction. OLE Automation can be defined as "any process that performs outside of the application controlling the process." To work with an OLE Control, developers expect a visual behaviour; to work with OLE automation, developers want something to occur. Based on this definition, Visual FoxPro ships with at least three OLE Automation Controls: Comm Control and the two MAPI Controls.
These controls automate various processes within the operating system environment. But they are OCXs and can be used by any other OLE Client as well. It makes it easier to explain because you can drop one of them into your application and then automate various processes. These controls are also easy to work with not only because they are documented in the Help file but because of their overall structure. Knowing about an applications structure is vital to building OLE into an application.
The Comm Control is a control over your serial communications under Windows NT or Windows 95. By calling various methods and events, you can build support for serial communications into your application.
The MAPI controls provide similar functions for electronic mail but the MAPI Message control is also a good introduction into using Collections. The MAPI Message control acts as a container for all of the messages in the current Mail session.
The functionality provided by the MAPI controls is only available under Windows 95 and Windows NT.
Here is sample code showing you how to use the MAPI Controls in a Visual FoxPro application. The important part about working with controls is to realize that this is still OLE Automation; Visual FoxPro is just giving us a Visual clue as to its existence.
Create an instance of a MAPI Session
Create a form and drop both controls on it. Name them SESSION and MESSAGE respectively.
DO form mapi name mapi
** attempts to sign on
** identifies internal session id
sess = mapi.session.sessionid
** sets the sessionid for the MAPI Control
** The Fetch unreadonly tells the MAPI Message to do exactly That.
** Its a logical property.
** Retrieves messages in the INBOX
** Returns number of messages
** Identifies current message being looked at
** Sets the current message
mapi.message.MsgIndex = 1
** Information from the message
sub = mapi.message.msgsubject
text = mapi.message.msgnotetext
daterec = mapi.message.msgdatereceived
Many OLE Server applications contain object "collections" and a pointer to the current object. This allows the application to handle multiple documents that may be open at once as well as a wide range of complementary objects. Microsoft Excel uses a "worksheet collection" to identify the various worksheets that are open. Visio uses a "pages collection" to refer to the various pages of a drawing file. Collections are implemented in different ways: some are accessed as object arrays with pointers to an actual live object as shown below: Others are pointers but also have additional properties for the entire collection.
From the FoxPro help file:
In code, a collection is an unordered list in which the position of an object can change whenever objects are added to or removed from the collection. You access an object in a collection by iterating through the collection, using the Count property of the collection. The Count property returns the number of items in the collection. Additionally, you can use the Item method to return an item in a collection.
Collections are everywhere in MS Project and Visio which are our two major examples in this session.
Working with MS Project and Visual FoxPro
Here is a diagram displaying the Object structure of MS Project based on MS Project version 4.0. Some of the details may have changed in MS Project 95.
The important thing to realize is the number of collections involved in this program. There is a wealth of information available in the Office Developers Kit which can be found on the MSDN CDs. The Office Developers Kit also goes into great details as to the various properties and methods available to developers.
The objects provided by Microsoft Project can be divided into the following categories:
The diagram above shows an option for Object and Collection. This indicates that there are in fact, two objects: one is a collection or array of the other. For example, the Windows box is actually made up of a Windows Collection and a Window object. The Window object can be accessed as WINDOWS( n ).
An interesting thing in the MS Project object model is that the Shift property is not a collection. It only has three properties Shift1, Shift2, and Shift3.
When a MS Project object is added into a form, MS Project starts. When accessing the project in a running form for the first time, MS Project loads itself into memory and therefore takes a few seconds. Once loaded, however, it runs very quickly.
The Duration property is calculated into minutes, allowing for fairly accurate reporting. When a TASK.DURATION returns 480, divide the number by 60 to view it by hours.
MS Project doesnt update the screen properly unless the Project is open in the MS Project window. In order to do this, the form must open the contained object when it wants to refresh it.
Steps to include MS Project in a Form.
Create a form and add an OLE Container to it with MS Project as the OLE Object.
Run the Form as "DO FORM Project NAME ProjForm" and double-click on the OLE Object.
** Create a pointer to MS Project itself
ProjApp = projform.project.Application
** Make it easier to access the object
Proj = projform.project
** Returns the number of resources in the project
** Add one too many so we can delete it again
ProjRes1 = Proj.resources(1)
ProjRes1.Name = "Andrew Ross MacNeill"
ProjRes1.Standardrate = 25
Proj.Tasks(1).Name = "Software Development"
Proj.Tasks(1).Start = DATE()
Proj.Tasks(1).End = DATE()+30
Proj.Tasks(1).Finish = DATE()+30
ProjApp.Visible = .t.
ProjApp.Visible = .F.
Proj.Tasks(1).Resources(1).Id = 1
Proj.Tasks(1).ResourceNames = "Andrew MacNeill"
Proj.tasks(2).Name = "Ship Product"
Proj.tasks(2).start = DATE() + 5
Proj.tasks(2).Finish = DATE() + 30
Proj.Tasks(2).ResourceNames = "Andrew MacNeill"
Proj.Visible = .t.
ProjApp.Visible = .t.
Proj.tasks(3).Name = "One Day"
Proj.tasks(3).start = DATE()
Proj.tasks(3).Finish = DATE()+1
? Proj.Tasks(3).Duration && Number of minutes
? Proj.Tasks(2).Duration/60 && view number of hours
? Proj.Tasks(2).Duration/60/5 && View number of days
Proj.Tasks(1).Successors = "2"
Proj.Tasks(1).Successors = "2,3"
** Since the project was already opened I noticed I had a task 6
Proj.Tasks(2).Name = "Ending"
Proj.Tasks(1).Start = DATE() + 5
Proj.Tasks(2).Start = DATE() + 6
Proj.Tasks(6).Name = "Ending"
Proj.Tasks(6).Resources = "1"
Proj.Tasks(6).Resources(1).Id = "1"
Proj.Tasks(6).ResourceNames = "Andrew MacNeill"
Proj.Tasks(1).ResourceNames = "Andrew MacNeill"
** The following item will cause an error in Project
** because Project doesnt allow you to have task Starting before
** the beginning of the project.
Proj.Tasks(1).Start = DATE() -2
Proj.Tasks(1).Start = DATE()
Proj.Tasks(6).Start = DATE()
Proj.Tasks(1).Duration = 20
Proj.Tasks(6).Duration = 20
Proj.Tasks(1).Duration = 20*60
Proj.Tasks(6).Duration = 20*60
** This is an automatic process that Project will use
** to calculate new start/finish dates for tasks in order
** that they may meet the resource restrictions
The above code hints at a very powerful reason to consider using MS Project with Visual FoxPro. Moreso than using Excel for performing complex calculations, Project can provide resource and task scheduling internally. As well, Project has a proven structure behind it. By following the object model in Project, it is possible to build incredibly robust scheduling and maintenance applications.
Working with Visio and Visual FoxPro
Just like working with MS Project, Visio is also an application controlled in great part by containers.
Important to remember when working with any OLE Automation:
If you have large periods of time when working with an object, always reverify its environment in case the user has made some changes in it.
To make changes to items in Visio, the Cells collection is vital. This collection contains specific information about an objects Width, Geometry and more. Everything in Visio is stored in a Cell by using a formula.
Setting up an OLE Link to Visio
As discussed above, an OLE Server can have a visual component in a FoxPro application. By attaching the control into a form, the FoxPro application gains a visual representation that might be useful. With this in mind, here are two ways of creating the link to Visio:
Adding Visio to a Form
Select Options from the Tools menu.
Click on the Controls Tab.
In the controls list, check that Visio Drawings are available on the toolbar.
Create a new form.
Insert a Visio Control by selecting OLE Controls and drag the Visio Drawing onto the form.
The link in the form is that the control has an Application member that is the real pointer into Visio.
Creating a pointer to a Visio Application Object.
Here is code to demonstrate a programmatic approach to working with a Visio object.
PUBLIC AppVisio, DocsVisio, AppPage,DocPage, MasterDoc, MasterObject, RectObject, WindowObject
appVisio = CREATEOBJECT("Visio.application")
DocsVisio = AppVisio.Documents
** Creates a pointer to a new visio document based on the Rumbaugh method
VisioDoc = DocsVisio.Add ("basic.vst")
** These are two references to the same page.
AppPage = AppVisio.ActivePage
DocPage = VisioDoc.Pages(1)
DocPage.Name = "Demonstration for Conference"
** Now Pages Contain SHAPES...
** SHAPES can either be drawn outright using the DrawLine, DrawOval and DrawRectangle Methods
** Or they can be based on Master Shapes
** Since our example opened up the FLOWCHART VST...I know there is a document
** called FLOWCHRT.VSS (or stencil)
MasterDoc = AppVisio.Documents("basic.vss")
** Now I can pull out a Master Object
MasterObject = MasterDoc.Masters("Rectangle")
** This drops the object onto the document page
RectObject = DocPage.Drop (MasterObject, 4,5)
** Now then....
** How do we find out about what Masters we have
** Each Visio Collection has a count property...
** This logic can also be used for going thru ANY Collection
FOR lni = 1 TO MasterDoc.Masters.Count
** ? MasterDoc.Masters(lni).Name
** We can also edit the text for an object
RectObject.Text = "Adding Diagram"
** Visio Application objects also have collections of Windows and Active Windows
** This allows you to perform your ZOOM Controls...
WindowObject = AppVisio.ActiveWindow
WindowObject.Zoom = 1.5
** This draws an oval but it doesn't give us a pointer
** so we undo it
** and create a pointer
Oval = docpage.DrawOval(5,3,4,5)
** This next command may seem logical but it doesn't work...
** With Ovals, you need to set the Center
** With Visio, you can also add other stencils
** As well as identify different STYLES to use.
** with these styles you can then change an object's appearance.
RectObject.FillStyle = "70% Gray Fill"
RectObject.FillStyle = "50% Gray Fill"
RectObject.FillStyle = "25% Gray Fill"
RectObject.FillStyle = "10% Gray Fill"
** Changing an object's width requires access to its Cells collection
** where you change the CELL Formula for the WIDTH.
RectCell = RectObject.Cells("Width")
RectCell.Formula = "2.5 in."
RectCell.Formula = "5 in."
RectCell.Formula = "1 in."
RectCell.Formula = "2.5 in."
OLE Automation is incredibly powerful, not just for a few applications that support it but for entire corporations. Tools like Visual Basic 4 make it easy for developers to build their own OLE Automation servers that can provide real business-like functionality. OLE is one step towards the data-centric world that will make computers a little easier for everyone - by using the right tools for the right jobs. We should make use of it whenever and wherever we can.
Andrew Ross MacNeill is the Software Architect at Melton Technologies Inc, a company based in North Carolina, Canada and Europe. Andrew is based in Ottawa, Canada but spends most of his time in cyberspace. He is the author of several public domain tools available on CompuServe. He can be reached on CompuServe at 76100,2725 or on the Microsoft Network as AndrewMacNeill@msn.com. He can also be reached via fax or voice mail at (613) 596-3313.
Thinking in a Document-Based World (Attachement)
Andrew Ross MacNeill
Melton Technologies Inc.
Looking at products like Windows 95 and Microsoft Office 95, it becomes evident that software is finally taking its first real steps into an environment that has been talked about for many years but has eluded us, the Document-based or Data-centric universe. Object-oriented programming is also a major step in this way because it makes us think of objects as components instead of programming structures.
The selection of an object, such as a menu item, determines a set of actions for that object. These actions help define the object in the context of the application. In other words, the nature of the data determines the menu. As a general rule:
The Nature of the Object determines the Options Available to it
With database solutions, it is common to regard an application as programs performing functions on various files. In the real world, however, programs are functions that these objects do. For example, a person is not walked; a person walks. A document may contain calculations and graphs from a spreadsheet, graphics from an artist and a plan from a project manager. These objects are not reactive but proactive and developers should think about them in this way. This is a change from current xBase syntax where a table is "used" and then functions are performed to it. With this approach, developers need to be aware of the objects and their place within the application environment.
Lets look at this in more detail. The act of adding an invoice is something specific that happens to an invoice; it is not a particular attribute of the adding function. The individual functions of creating, editing and deleting are only a part of the various processes that can happen to an invoice or to any other object. It is important to think about the object being acted upon; in this case, the invoice. When a new invoice is created, it has different attributes and functions than when adding a new client. A new invoice may have a customer number, an invoice date, an invoice number and a total on it. The validation of each of these attributes is something that can be considered unique to the invoice object.
The invoice object may share some attributes or functions with other objects. When developing, it is often useful to "share" these components of an application. If an invoice is only one part of a larger transaction, this sharing might be provided with a similar menu or screen interface that "opens" an entire transaction. The transaction "document" might contain invoice, client and posting objects. These objects are different parts of the same transaction. Each object may have specific functions but the interface treats the entire transaction as the current "document".
Its also important to note that this approach helps move developers into FoxPro 3.0 and object-oriented programming (OOP). In OOP, objects have attributes and methods. Methods are essentially programs that control what happens to an object. With the Invoice object, there might be an AddInvoice method. When an application creates a new invoice, instead of USEing the INVOICE table and issuing an APPEND BLANK command, OOP concepts would have a program pass an AddInvoice message to the Invoice object. This message would run the AddInvoice method which would control all of the validation related to the Invoice object. With our transaction application, a program would pass an OpenTransaction message to a Transaction object. The OpenTransaction method would know enough to open the Invoice, Client and Posting objects as well as the Transaction object.
This approach to interface design is being implemented in many applications, not just database programs. OLE 2.0 and Windows 95 are two major components of introducing new users to a document-based world. When we design our applications, it may be useful to think about our databases this way. Not only does it help prepare us for an object-oriented way of thinking, it also helps us think in terms of the real-world, a place that developers very easily lose track of.