Microsoft Office Automation with Visual FoxPro

Adding server-specific operations

So far, all the methods we've defined have been common to all the servers. While they do make it easier to write Automation code, they're just the beginning. The next step is to wrap the operations specific to the individual servers. 

But the Office servers have dozens of methods. How do you know which ones to wrap? That's easy-wrap the ones you use. As you write Automation code, pay attention. Keep in mind that the goal of wrapping the servers is to make your life simpler. Think in terms of what you're trying to accomplish when you use a particular method of the server. 

The second time you find yourself performing a particular task, it's time to put a wrapper on it. But just because you've wrapped it, don't assume you're done with it. As you see how you really use it, you'll probably have to clean it up. Among the questions you need to ask are what parameters it ought to have, whether the wrapper class needs some properties to support this method, whether the method really needs to be broken into multiple methods, and whether several of the server's methods should be grouped into a single wrapper method because you're always going to use them together. What you want to end up with is a set of methods with meaningful names and not too many parameters, each of which accomplishes a single task, but which you can put together to perform complex operations.

To make your job a little bit harder, it turns out that your wrapper classes will probably need to work differently for different applications. We hinted at that earlier when we talked about the CloseServer method-in some applications, it's appropriate to close all open documents without saving them; in others, you need to save every open document; and in others, the user has to make that choice. It's the same way with other server-specific tasks-both the set of operations you need and the way they work vary based on what you're trying to accomplish. 

Fortunately, in an object-oriented environment like VFP, it's easy to deal with such variations. Subclassing provides one technique for doing so, though it can lead to maintenance headaches, if you need too many variations. An alternative is to use properties and parameters to handle application-specific needs. If a particular operation needs to be implemented in several very different ways, you may even want to use a separate class to handle it. Then, the server-specific subclass can determine the appropriate implementation at run-time and instantiate the class it needs. (This is the Strategy pattern-for details, see Design Patterns: Elements of Reusable Object-Oriented Software by Gamma, Helm, Johnson, and Vlissides.) 

To get you started, here are wrapper methods for one common operation in each of the Office servers.

Wrapping Word-from table to table

As we discussed in Chapter 5, "Intermediate Word," Word tables are handy for displaying FoxPro data. One of the things you're most likely to do when sending VFP data to Word is convert a VFP cursor of some sort into a Word table. The method here, DBFToTable, takes the data in a workarea, whether it represents a cursor, table, or view, and creates and populates a Word table containing that data. The method accepts four parameters, all optional: the range where the table is to be placed, the alias for the cursor, and two logical flags. The first flag indicates whether to leave an empty row at the top of the table for headings, and the second determines whether the AutoFit method is used to resize the table once it's been filled. Here's the code:

* cusWord.DBFToTable

* This method puts the data in a cursor, view, or table

* into a Word table. It creates one table row per record

* with one column for each field. Returns a reference to

* newly created table; .null. if the table can't be

* created

LPARAMETERS oRange, cAlias, lAddHeader, lAutoFit

* oRange - where to put the new table. If omitted,

* add at current insertion point.

* cAlias - the alias for the workarea containing

* the cursor/view/table. If omitted,

* use current workarea.

* lAddHeader - include a header row in the table?

* lAutoFit - automatically resize columns?

LOCAL nOldSelect, nRows, nCols, oTable, nCurRow, nCol, nActualCol

* nOldSelect = workarea selected when called

* nRows = number of rows to add

* nCols = number of columns to add

* oTable = holds a reference to the newly created table

* nCurRow = the current row receiving data

* nCol = column counter

* nActualCol = indicates which column to insert into table

* Check parameters

nOldSelect = SELECT()

IF EMPTY( cAlias )

cAlias = ALIAS()

IF EMPTY( cAlias )

This.TellUser("Must specify cursor")

RETURN .NULL.

ENDIF

ELSE

IF USED( cAlias )

SELECT (cAlias)

ELSE

This.TellUser("No such cursor is in use")

RETURN .NULL.

ENDIF

ENDIF

IF VarType( lAddHeader ) <> "L"

This.TellUser("Use a logical value to indicate whether or not " + ;

"to add a header row")

SELECT (nOldSelect)

RETURN .NULL.

ENDIF

IF VarType( lAutoFit ) <> "L"

This.TellUser("Use a logical value to indicate whether or not " + ;

"to resize columns")

SELECT (nOldSelect)

RETURN .NULL.

ENDIF

* Now make sure there's a document.

IF NOT (This.IsServerOpen() AND This.IsDocOpen())

This.TellUser("Word must be open with a document ready.")

SELECT (nOldSelect)

RETURN .NULL.

ENDIF

* Check oRange parameter

IF VarType( oRange ) <> "O"

oRange = This.oServer.Selection.Range()

ENDIF

* Ready to go. Add the table at the current insertion point

WITH This.oDocument

nRows = RECCOUNT()

IF lAddHeader

nRows = nRows + 1

ENDIF

* Determine the number of columns to create. 

* Omit any General fields

nColumns = 0

FOR nCol = 1 TO FCOUNT()

IF TYPE(FIELD(nCol)) <> "G"

nColumns = nColumns + 1 

ENDIF

ENDFOR

oTable = .Tables.Add(oRange,nRows,nColumns)

ENDWITH

WITH oTable

IF lAddHeader

* Set the first row as a header row to be repeated on subsequent pages

.Rows(1).HeadingFormat = .T.

nCurRow = 2

ELSE

nCurRow = 1

ENDIF

* Now add data to rows, skipping any general fields

SCAN

nActualCol = 1

FOR nCol = 1 TO .Columns.Count

* Eliminate general fields

DO WHILE TYPE(FIELD(nActualCol)) = "G"

nActualCol = nActualCol + 1

ENDDO

.Cell[ nCurRow, nCol ].Range.InsertAfter( ;

TRANSFORM(EVAL(FIELD(nActualCol))))

nActualCol = nActualCol + 1

ENDFOR

nCurRow = nCurRow + 1

ENDSCAN

IF lAutoFit

.Columns.AutoFit()

ENDIF

ENDWITH

RETURN oTable

The code itself is pretty straightforward. What makes the method worth writing is that it takes a task we're likely to perform pretty frequently and turns it into a single call. Listing 2 is another program that uses the Word wrapper class. Like the program in Listing 1, it creates an Automation server, and opens a new document. Then, it creates a cursor based on the Supplier table from TasTrade, sends that data to the new document, then saves the document and closes the server. This program is included as BuildSupplierTable.PRG in the Developer Download files available at www.hentzenwerke.com. Figure 1 shows part of the resulting document.

Listing 2. Using a Word-specific method. This program creates a Word table with one method call from the Word wrapper class.

* Build a Word table using the wrapper class

#DEFINE CR CHR(13)

LOCAL oWord

* Open Word

oWord = NewObject("cusWord","automation")

WITH oWord

.OpenServer()

IF NOT .IsServerOpen()

WAIT WINDOW "Couldn't open Word."

RETURN .F.

ENDIF

* Create a document

.NewDocument()

* Check whether document is open 

IF .IsDocOpen()

* Put some text in the new document

OPEN DATABASE _SAMPLES + "TasTrade\Data\TasTrade"

SELECT Company_Name, Contact_Name, Contact_Title, Phone ;

FROM Supplier ;

ORDER BY 1 ;

INTO CURSOR SupplierList

oRange = .oDocument.Range()

.DBFToTable(oRange, ,.F., .T.)

USE IN Supplier

USE IN SupplierList

ENDIF

* Save the new document

.SaveDocument(, SYS(2023) + "\suppliers.doc")

* Close Word

.CloseServer()

ENDWITH

RETURN

Figure 1. List of suppliers. This table was generated by Listing 2, which uses the Word wrapper class. The DBFToTable method handles all the details of moving data from a cursor to a Word table.

Wrapping Excel-from table to graph

As we saw in Chapter 9, "Excel's Graphing Engine," Excel has a wonderful graphing engine. The ChartWizard method has many parameters, most of which don't change over the course of your application. The method here, MakeGraph, takes a range of data and creates a chart sheet displaying the data. It accepts three parameters: one required, the range of data as an object, and two optional, a string to name the chart sheet, and the title of the graph. 

A series of custom properties hold the defaults for the chart. The property oChart stores an object reference to the chart, in the event that you want to directly edit the chart. nChartType determines the type of chart (bar, column, and so on), and nChartFormat determines which of Excel's predefined formats is used (for a complete description, see "Chart types" in Chapter 9). The default for those two is column type, using the first predefined format. Your program can easily change these properties to generate different kinds of charts (or add optional parameters to override the property settings). The nChartPlotBy property determines whether the series are in rows or columns; the default is columns. Several other parameters in the call to ChartWizard are hard-coded, but these, too, could be switched to refer to properties of the cusExcel object, or to parameters passed into the MakeGraph method. Here's the code:

* cusExcel.MakeGraph

* This method wraps Excel's ChartWizard method.

* It creates a graph from a range of data.

LPARAMETERS oGraphRange, cSheetName, cTitle

* Verify oGraphRange

IF VarType(oGraphRange) <> "O"

This.TellUser("The graph range must be an object.")

RETURN .F.

ENDIF

* Check cSheetName

IF VarType(cSheetName) <> "C"

cSheetName = .NULL.

ENDIF

* Check cTitle

IF VarType(cTitle) <> "C"

cTitle = ""

ENDIF 

WITH This.oServer

* Generate the chart

.Charts.Add()

IF NOT ISNULL(cSheetName)

.ActiveChart.Name = cSheetName

ENDIF

* The only constants are that there is one row and one column 

* for series and category labels, and it has a legend 

* (corresponds to the three 1's)

This.oChart = .ActiveChart.ChartWizard(oGraphRange, ;

This.nChartType, This.nChartFormat, This.nChartPlotBy, ;

1, 1, 1, cTitle, "", "", "")

ENDWITH

The first part of the code performs a series of checks to ensure that you've passed good data. Then it adds the chart sheet, renames it (if a valid name is passed), and then formats the chart using the ChartWizard method. What makes this so special is that you now need to remember three parameters-the range to graph, what to call the resulting chart, and what to title the chart-instead of a long series of ChartWizard parameters. If your graphs need special formatting (such as incorporating the corporate colors into the background, or changing the axes' font face, size, and color), you can include it here. Imagine how much time you save as a developer writing this code once. 

Listing 3 is a program that uses the Excel wrapper class with the Excel-specific MakeGraph method. It instantiates an Automation server and opens a new document (a Workbook, in Excel's case). Then, it creates a cursor based on the Product table from TasTrade, choosing only the Confections records. It sends that data to the new workbook, creates the graph, saves the document, and closes the server. This program is included as BuildGraph.PRG in the Developer Download files available at www.hentzenwerke.com. Figure 2 shows part of the resulting document.

Listing 3. Using an Excel-specific method. This program creates an Excel graph with one method call from the Excel wrapper class.

* BuildGraph.PRG

* Demonstrate the Excel wrapper class with 

* Excel-specific MakeGraph method.

LOCAL oExcel, oRange, cRow

* Open Excel

oExcel = NewObject("cusExcel","automation")

WITH oExcel

.OpenServer()

IF NOT .IsServerOpen()

WAIT WINDOW "Couldn't open Excel."

RETURN .F.

ENDIF

* Create a workbook

.NewDocument()

* Check whether workbook is open 

IF .IsDocOpen()

* Get the product data cursor

IF NOT DBUSED("TasTrade")

OPEN DATABASE (_SAMPLES + "TASTRADE\DATA\Tastrade")

ENDIF

IF NOT USED("Products")

USE Products IN 0

ENDIF

SELECT English_Name, ;

Unit_Price, ;

Unit_Cost ;

FROM Products ;

WHERE Category_ID = " 3" ; 

AND NOT Discontinued ;

INTO CURSOR GraphProduct

WITH .oDocument.Sheets[1]

* Put in data headings

.Range("A1").Value = "Product Name"

.Range("B1").Value = "Unit Price"

.Range("C1").Value = "Unit Cost"

* Put the cursor's data into the spreadsheet

SELECT GraphProduct

SCAN

cRow = ALLTRIM(STR(RECNO() + 1))

.Range("A" + cRow).Value = GraphProduct.English_Name

.Range("B" + cRow).Value = GraphProduct.Unit_Price

.Range("C" + cRow).Value = GraphProduct.Unit_Cost

ENDSCAN

* Set up the range

oRange = .Range("A1:C" + cRow)

ENDWITH

* Build the graph. 

.MakeGraph(oRange, "Confections", "Price vs. Cost of Confections")

ENDIF

RELEASE oRange

* Save the new workbook

.SaveDocument(, SYS(2023) + "\sample.xls")

* Close Excel

.CloseServer()

ENDWITH

* Clean up the data

CLOSE DATA ALL

RETURN

Figure 2. A graph showing the price vs. the cost of confections. This graph was generated by Listing 3, which uses the Excel wrapper class. The MakeGraph method handles all the details of generating the graph.

The example points out another Excel-specific method you're likely to want-a method to send data from a VFP cursor to a Worksheet. With such a method, the code in Listing 3 would be simplified even further. 

Wrapping PowerPoint-standardizing text

In Chapter 10, "PowerPoint Basics," we discuss the many properties available for text frames. Good presentation design dictates that font face, size, and color should remain constant throughout your presentation, to avoid the ransom-note effect. Of course, there are times when changing one or more of the properties is necessary to call attention to the phrase. We've created an AddTextFrame method to standardize the text properties, yet allow for quick changes.

Before we get into the AddTextFrame method, we must discuss a serious inconsistency between PowerPoint and the other servers. The other servers always have an active document of some sort (ActiveDocument in Word, ActiveWorkbook, ActiveSheet, and ActiveChart in Excel) that's always available. PowerPoint works a bit differently. When PowerPoint isn't visible, the ActivePresentation property isn't available (in fact, accessing it generates an error). In addition, while there isn't an ActiveSlide property, you can query the View for the SlideIndex property. However, when PowerPoint isn't visible, Views aren't available (accessing Views generates an error in this case). We suspect that PowerPoint likes to be visible (since it is a very visual product), and when it's not visible, it just doesn't accept that there are active views or presentations, since nobody can view them. 

To make things work properly, we need to make a few changes. The first is to the oDocument_Access method, which verifies that oDocument still points to a valid document (see "Keeping track of the active document" earlier in this chapter). We've added a check to see whether the server is visible before setting oDocument to ActivePresentation, to prevent invisible instances from raising an error. There's a side effect here: if you run PowerPoint nonvisually, you must keep oDocument current on your own (by updating it whenever you change presentations). Here's the changed code for the oDocument_Access method:

* Make sure the property points to the currently active document.

IF This.IsServerOpen() AND This.IsDocOpen()

* PowerPoint has no Active properties when it is not Visible.

* Be sure that you don't change the active Presentation when

* PowerPoint is not visible (if you do, manually update oDocument).

IF This.oServer.Visible <> 0 

This.oDocument = This.oServer.ActivePresentation

* Else, assume that the developer's kept up oDocument, and assume

* whatever it's pointing to is valid.

ENDIF

ELSE

This.oDocument = .NULL.

ENDIF

RETURN THIS.oDocument

The other change we need to make is to track the current slide. This requires a custom oCurrentSlide property and a custom AddSlide method. oCurrentSlide, as its name implies, tracks the current slide. The new AddSlide method is used to add new slides and update oCurrentSlide. It takes two parameters: the slide layout value (see "Using the slide layouts" in Chapter 10 for a list of the values) and the index of the new slide in the slide show. Here's the code:

* cusPowerPoint.AddSlide

* Wrap the Slides.Add method in order to 

* keep track of the current slide.

LPARAMETER nSlideLayout, nNewIndex

* nSlideLayout = The number of the slide layout. Optional. 

* (Corresponds to ppLayout constants.)

* If omitted, ppLayoutBlank is used.

* nNewIndex = The Index of the new slide. Optional.

* If omitted, the index of the slide in oCurrentSlide

* is used. If no slide in oCurrentSlide, 1 is used.

WITH This 

* Check Parameters

IF PARAMETERS() = 0

nSlideLayout = ppLayoutBlank

ENDIF 

IF PARAMETERS() <= 1

IF TYPE(".oCurrentSlide") = "O"

nNewIndex = .oCurrentSlide.SlideIndex

ELSE

nNewIndex = 1

ENDIF

ENDIF

* Add the slide

.oCurrentSlide = .oDocument.Slides.Add(nNewIndex, nSlideLayout)

ENDWITH

The AddTextFrame method places a text box in the current presentation (referenced by the oDocument property) on the current slide (referenced by the oCurrentSlide property). AddTextFrame uses the custom TextFrameFont, TextFrameFontSize, and TextFrameTextColor properties to set the look of the text. The idea is that you set these properties once, and change them only when needed (remembering to set them back when you're done). The same concept holds for TextFrameHeight and TextFrameWidth, which set the default height and width for the text frame. By storing all these properties, you can concentrate on the items you need, which are passed as parameters: the text, the distance from the top of the slide, in points, and the distance from the left of the slide, in points. Optionally, you can pass the height and width of the frame (in points) to override the default properties. The method returns a reference to the new frame, in case you need to further manipulate the text frame. Here's the code for the AddTextFrame method:

* cusPowerPoint.AddTextFrame

* Add a text box to the current slide of the current presentation.

LPARAMETERS cTextString, nLeftPoints, nTopPoints, ;

nWidthPoints, nHeightPoints

* cTextString = String to place in text frame

* nLeftPoints = The left edge, in points

* nTopPoints = The top edge, in points

* nWidthPoints = The width of the text frame, in points.

* Optional; uses TextFrameWidth property if not passed.

* nHeightPoints = The height of the text frame, in points.

* Optional; uses TextFrameHeight property if not passed.

LOCAL oNewFrame, nCurrentSlide

* oNewFrame = The object reference to the new TextFrame

* nCurrentSlide = Index of the current slide

* If there's no text string, just return

IF EMPTY(cTextString)

RETURN .F.

ENDIF

* Check the left and top, to be sure they're numeric

IF TYPE("nLeftPoints") <> "N"

This.TellUser("Must specify the left edge in points.")

RETURN .F.

ENDIF

IF TYPE("nTopPoints") <> "N"

This.TellUser("Must specify the top edge in points.")

RETURN .F.

ENDIF

* Check the width and height -- if not passed, set them

* to the default; if passed, ensure they're numeric

IF TYPE("nWidthPoints") <> "N"

nWidthPoints = This.TextFrameWidth

ENDIF

IF TYPE("nHeightPoints") <> "N"

nHeightPoints = This.TextFrameHeight

ENDIF

* Check the status of oCurrentSlide

IF TYPE("This.oCurrentSlide") <> "O"

This.TellUser("oCurrentSlide property must be set.")

RETURN .F.

ENDIF

* Place the text frame

oNewFrame = This.oCurrentSlide.Shapes.AddTextBox(1, ;

nLeftPoints, nTopPoints, ;

nWidthPoints, nHeightPoints)

WITH oNewFrame

* Set it invisible, to maximize performance

.Visible = .F.

* Add the text to the text frame, and set the

* rest of the properties

WITH oNewFrame.TextFrame

WITH .TextRange

.Text = cTextString

.Font.Name = This.TextFrameFont

.Font.Size = This.TextFrameFontSize

.Font.Color.RGB = This.TextFrameTextColor

ENDWITH

.MarginLeft = 0

.MarginTop = 0

.MarginRight = 0

.MarginBottom = 0

ENDWITH

.Visible = .T.

ENDWITH

RETURN oNewFrame

Listing 4 is a program that uses the PowerPoint wrapper class. It instantiates an Automation server and opens a new document (a Presentation, in PowerPoint's case). Then it adds a new slide with the AddSlide method, and uses the AddTextFrame method to add three text frames. This program is included as BuildPptSlide.PRG in the 

Developer Download files available at www.hentzenwerke.com. Figure 3 shows part of the resulting document.

Listing 4. Using a PowerPoint-specific subclass. This program creates a PowerPoint slide with one call to the PowerPoint wrapper class.

* BuildPptSlide.PRG

* Demonstrate the PowerPoint wrapper class with 

* PowerPoint-specific methods

#DEFINE CR CHR(13)

#DEFINE ppLayoutTitle 1

#DEFINE msoIn2Pts 72

LOCAL oPowerPoint, OrigTextColor

* Open PowerPoint

oPowerPoint = NewObject("cusPowerPoint","automation")

WITH oPowerPoint

.OpenServer()

IF NOT .IsServerOpen()

WAIT WINDOW "Couldn't open PowerPoint."

RETURN .F.

ENDIF

* Create a document

.NewDocument()

* Check whether document is open 

IF .IsDocOpen()

* Add a slide using the wrapper class. No parameters

* means it will add a blank slide as the first slide.

.AddSlide()

* Put some text frames on the new slide

.AddTextFrame("First text box.", 2 * msoIn2Pts, 2 * msoIn2Pts)

.AddTextFrame("Second text box.", 6 * msoIn2Pts, 2 * msoIn2Pts)

OrigTextColor = .TextFrameTextColor

.TextFrameTextColor = RGB(0, 255, 255) && Cyan

.AddTextFrame("Third text box.", 4 * msoIn2Pts, 4 * msoIn2Pts)

.TextFrameTextColor = OrigTextColor

ENDIF

* Save the new document

.SaveDocument(, SYS(2023) + "\sample.ppt")

* Close PowerPoint

.CloseServer()

ENDWITH

RETURN

Figure 3. A slide showing simple text frames added with the AddTextFrame PowerPoint-specific method. This slide was generated by Listing 4, which uses the PowerPoint wrapper class.

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved