3.2 Working with servers | Microsoft Office Automation with Visual FoxPro

Working with servers

A few Visual FoxPro language features and interface components make it easier to write and maintain Automation code. Here s a look at some things to remember as you work in the wild world of Automation.

SET OLEOBJECT

This VFP command determines whether VFP searches the registry when using CreateObject() or GetObject(). When it s set to ON, VFP searches the registry; when it s set to OFF, that search is skipped. Why does this command exist? The last place VFP looks to find an object is in the registry. Before searching the registry, it loads OLE support, which takes up memory. If your application doesn t require OLE support, setting OLEOBJECT to OFF provides a bit of a performance enhancement. However, if you re using Automation, you need OLE support, as well as the ability to find the server in the registry. If you attempt to instantiate an Automation server while SET OLEOBJECT is set to OFF, an error like "Class definition WORD.APPLICATION is not found" is generated.

Use WITH ENDWITH

Much of the code you write to automate any server involves setting properties or calling methods. It s common to have long stretches of code that consists of not much more than references to properties and methods, with perhaps a little bit of arithmetic or logic thrown in. You can make that code far more readable (and thus easier to debug and maintain) by using VFP s WITH ENDWITH command.

A series of commands that all begin with something like oWord.ActiveDocument.Tables[ 3 ].Rows[ 7 ] just isn t going to lend itself to readability. Instead, surround the group like this:

WITH oWord.ActiveDocument.Tables[ 3 ].Rows[ 7 ]

* put the commands here with a dot in front of each

ENDWITH

In fact, you can nest WITH commands. As you walk down the object hierarchy, doing a few things at each level, set up a WITH statement for each level something like this:

#DEFINE wdAlignRowCenter 1

#DEFINE wdRowHeightExactly 2

WITH oWord.ActiveDocument

* Do some things to the document as a whole, like

.Save && save using current filename

* Then move on to the table.

WITH .Tables[ 3 ]

* Now issue commands aimed at the table as a whole

.AllowPageBreaks = .T. && allow table to break across pages

* Then, when you're ready to talk to the single row.

WITH .Rows[ 7 ]

* Now issue the commands for the one row

.Alignment = wdAlignRowCenter

.HeightRule = wdRowHeightExactly

.Height = .5

ENDWITH

ENDWITH

ENDWITH

The nesting makes it clear to the reader which WITH each property belongs to. VFP, of course, has no difficulty figuring it out. There s an added bonus besides readability. Code like this runs faster FoxPro doesn t have to sort through multiple levels of hierarchy to find out what object a given property or method belongs to. In our tests, a fairly simple example that queried about a dozen properties at four levels below the Application object ran roughly twice as fast using nested WITHs as it did addressing each property directly.

Also, note that there s no rule that says the property or method has to be the first thing on the code line. It often works out that way, but it s perfectly fine to use them elsewhere. For example, the following code is acceptable:

WITH oExcel

nHeight = .Height

nWidth = .Width

ENDWITH

In fact, as shown by the example in the "Displaying the Office servers" section earlier in this chapter, you can call methods, perform calculations, and generally do anything you normally would inside a WITH ENDWITH pair, as long as you make sure to include the dot before the property or method name.

Use variables for object references

Another way to make your code more readable and speed it up is to assign complex object references to local variables. Even if a WITH ENDWITH pair isn t called for, you may be better off assigning something like oPowerPoint.ActivePresentation to a VFP variable with a name like oPres (or oPresentation, if you prefer). The variable name is easier to type and easier to read. As with the WITH statement, it gives VFP a direct route to the object you re interested in, rather than asking it to climb down the object hierarchy.

We tested the same example as for WITH (querying properties at various levels) and found that setting a local variable was as fast as or even a little faster than using WITH. We suspect the exact trade-off point varies, depending on factors like available memory, the number of references inside the WITH/to the local variable, and so forth. There s no question, however, that either approach is significantly faster than writing out a long reference to an Automation object. The more deeply nested the reference and the more times you need it, the more time you save.

When you use local variables, you may need to clean up afterwards. In some situations, these references can prevent the server from closing when you call the Quit method.

Loop with FOR EACH

In Chapter 2, "The Office Servers," we discussed the prevalence of collections in the Office object models (and in COM object models more generally). When you need to process all the members of a collection, VFP s FOR EACH loop is your best bet. FOR EACH lets you go through a collection (or array) without using a counter or worrying about how many members there are. Here s the syntax of FOR EACH:

FOR EACH oMember IN oCollection

* issue commands for oMember

ENDFOR

For example, to display the name of every open document in Word, you can use this code:

FOR EACH oDocument IN oWord.Documents

? oDocument.Name

ENDFOR

Note, by the way, that using FOR EACH implies the use of a local variable as described in the previous section the object reference used as the loop variable.