Microsoft Office Automation with Visual FoxPro

Communicating between Office applications

There are two basic methods to automate sharing data between two Office applications: one is to cut the selection to the clipboard, and paste it in the other application. This is a nice, simple method. The second method is to add it as an OLE object, which then gives the user a lot of control over the end product, as OLE s in-place editing features can then be used to tweak the appearance, if necessary. Several methods are available to add an OLE object.

Copying and pasting between applications

The interactive method of copying between applications goes something like this: open the application with the source data, select the source data, and use Cut or Copy to paste it to the clipboard. Then open the target application, locate the position to paste the data, and paste the contents of the clipboard. The same process applies to automated copying and pasting.

To illustrate automating a copy and paste procedure, we ll build a small spreadsheet, and place it into a Word document.

Setting up the source data

For this example, the source is a small spreadsheet. Chapters 7, 8, and 9 ("Excel Basics," "Advanced Excel Features," and "Excel s Graphing Engine," respectively) explain the details of setting up a spreadsheet, so we won t go into that here. Listing 1 shows the code to build a sample spreadsheet, which is shown in Figure 1. Listing 1 is available as SourceSetup.PRG in the Developer Download files available at www.hentzenwerke.com.

Listing 1. A sample Excel spreadsheet, which we ll use in our copying examples. See Figure 1 for the results.

* Clean out any existing references to servers.

* This prevents memory loss to leftover instances.

RELEASE ALL LIKE o*

* Make the following variables public for demonstration

* purposes, so you can inspect the objects when the

* program has finished. oSourceRange must be public

* if you're following along with the example in the book.

PUBLIC oExcel, oSourceRange

* Create an instance of Excel

oExcel = CreateObject("Excel.Application")

* Make Excel visible.

oExcel.Visible = .T.

oBook = oExcel.Workbooks.Add()

WITH oBook.Sheets[1]

.Range("A1").Value = "Quarter"

.Range("A2").Value = "First"

.Range("A3").Value = "Second"

.Range("A4").Value = "Third"

.Range("A5").Value = "Fourth"

.Range("B1").Value = "Sales Volume"

.Range("B2").Value = "100000"

.Range("B3").Value = "125000"

.Range("B4").Value = "150000"

.Range("B5").Value = "175000"

oSourceRange = .Range("A1:B5")

ENDWITH

Figure 1. The example spreadsheet to copy into a Word document. While this is a simple spreadsheet, any of Excel s robust features can be used. Chapters 7, 8, and 9 cover Excel in detail (see "Excel Basics," "Advanced Excel Features," and "Excel s Graphing Engine," respectively).

Notice that the next-to-last line of code sets a variable, oSourceRange. To make life a bit easier, it s worthwhile to set up a variable containing the data you want to copy while you still have the WITH ENDWITH constructs open. We ve made this one PUBLIC so that it s available to work along with the rest of the example. Normally, oSourceRange would be scoped more appropriately.

For Excel, this example shows the source data object to be a Range. It could also be a whole Worksheet or a Chart object. For Word, the source data is likely to be a range of text. For PowerPoint, the source data is likely to be a single slide. Be aware that you can use a whole multi-sheet Workbook, multi-slide Presentation, or multi-page Document as the source, but when copied to the target application, it may not view or print as you expect, since the target applications generally show only one worksheet, slide, or page.

Copy the data to the clipboard

Once you have the object reference, use the Copy method to get it to the clipboard. For Excel, the Range, Chart, and Worksheet objects have a Copy method. Using this method without parameters copies the object to the clipboard (parameters are available to allow you to copy the object to other locations within Excel). Issue the following command to place the source data on the clipboard:

oSourceRange.Copy()

Several Word objects, including the Range object, have a Copy method, which is used without parameters to copy to the clipboard. Likewise, the Copy method is available for a number of PowerPoint objects, including Slide, Shape, and TextRange objects.

Preparing the target application

Preparing the target application consists of opening the application and positioning your insertion point at the proper location. The Basics chapters for each of the Office servers cover this. To illustrate this concept, the example opens a blank document and adds a line of text. The cursor is positioned at the point where the spreadsheet is to be entered. Figure 2 shows the results of Listing 2, which is stored as TargetSetup.PRG in the Developer Download files available at www.hentzenwerke.com.

Listing 2. A sample Word document in which to place the spreadsheet from Listing 1. See Figure 2 for the results.

#DEFINE wdCollapseEnd 0

* Make oRange and oWord public to use during the exercise,

* while following along in the text.

RELEASE oWord, oRange

PUBLIC oWord, oRange

* Create an instance of Word.

oWord = CreateObject("Word.Application")

* Make Word visible.

oWord.Visible = .T.

oDocument = oWord.Documents.Add()

oRange = oDocument.Range()

oRange.InsertAfter("This is an example of copying a portion " + ;

"of a spreadsheet into Word." + CHR(13))

oRange.Collapse(wdCollapseEnd)

Figure 2. The sample Word document, which can receive the spreadsheet. This is a simple Word document, but it could be as advanced as you want it to be. Chapters 4, 5, and 6 ("Word Basics," "Intermediate Word," and "Advanced Word," respectively) describe the Word server in detail.

Because the range is collapsed to the end after inserting the text, oRange (declared PUBLIC for this interactive example) is positioned properly. Pay no attention to the actual cursor position in Figure 2 Ranges are independent of the cursor position. Be assured that oRange is properly positioned to paste the spreadsheet.

Pasting the data from the clipboard

In Word, the Range object describes a position in the document, so it makes sense to have a Paste method, which pastes the contents of the clipboard into the Range (so be sure to Collapse your Range, unless you want the contents of the Range replaced). The syntax is simply:

oRange.Paste()

Figure 3 shows the resulting spreadsheet in the Word document. The Excel spreadsheet is converted to a Word Table object.

Figure 3. The spreadsheet is pasted into the document. Notice that the spreadsheet is converted to a table.

Word is nice and easy the Range object has a Paste command that takes no parameters. Excel is a bit different. The Worksheet s Paste command takes one of two parameters: the first is the Range object where the source data goes on the sheet, and the second is used if the first is not specified, and is a logical parameter describing whether to link to the source data (the default is false).

PowerPoint is a little different. First, you must ensure that you are in the Normal view, rather than the Slide Sorter, Notes, or Slideshow views. Then, you can use the Paste method of the Shapes collection object or the TextRange object (for text strings only). The Shapes collection is appropriate for pasting objects from other applications, as in:

oNewObj = oSlide.Shapes.Paste()

This pastes the spreadsheet (or other object) in the middle of the specified slide. Manipulate the Top and Left properties of the newly placed object, oNewObj, to put it where it needs to be. It is interesting to note that a Word Range pasted into the Shapes collection becomes a single text box. However, when a Range from Excel is pasted into the Shapes collection, it is placed as an embedded Excel object. Generally, pasting as an embedded or linked object requires other commands, such as PasteSpecial or AddOLEObject.

PasteSpecial

The Paste method generally places a converted copy of the source data. The source data is converted to the most similar kind of object in the target application. Once converted, the data loses the editing capabilities of the original application. While in some circumstances, it is a benefit that an Excel spreadsheet becomes a Word table, other circumstances may need the editing capabilities of the original application.

This is where the PasteSpecial method comes in. You may have used this interactively, clicking on Edit|Paste Special from any of the Office applications menus. It brings up the dialog shown in Figure 4, which displays the source of the data, identifying not just the name of the application, but the range (in the application s notation) as well. The option buttons at the left indicate how to paste the link, and the list box indicates the formats into which the source data can be converted.

Figure 4. The Paste Special dialog box, obtained interactively from Word. This shows the many options available when using the PasteSpecial method.

Word s PasteSpecial method is available from the Range object. It takes a series of parameters, which parallel the options shown in Figure 4:

oRange.PasteSpecial( [nIconIndex], [lLink], [nPlacement],

[lDisplayAsIcon], [nDataType],

[cIconFileName], [cIconLabel])

The most important parameter is the lLink parameter, which defines whether the source data object should be linked or not. A linked object is essentially a pointer back to its source file. Any changes made to the source file show up in the document (when the object is refreshed), and any changes made to the object within the document can be saved directly to the source file (assuming the user saves the changes). If the object is not linked, it is considered "embedded," which means that the object is edited in-place in its native fashion, just as if it were linked, but there is no connection to the original source file. The object is completely encapsulated within the Word document.

To illustrate the PasteSpecial method to embed an Excel spreadsheet object, we ll use the same sample files as previously. The only change is from a call to the Paste method to a call to the PasteSpecial method. Figure 5 shows the results.

DO SourceSetup.PRG

oSourceRange.Copy()

DO TargetSetup.PRG

#DEFINE wdInLine 0

#DEFINE wdPasteOLEObject 0

oRange.PasteSpecial(,.F., wdInLine, , wdPasteOLEObject)

The Excel object is added as a Shape object in the Shapes collection. Notice in Figure 5 that the "Sales Volume" label is cut off. If the width of column B was adjusted prior to cutting, or if the range included the blank column C, this would appear correctly. The default is to show exactly the range specified no more and no less.

The user can edit any part of the spreadsheet by double-clicking on the newly added Shape object. The display changes to that shown in Figure 6. The user can complete the edits by clicking off the spreadsheet, anywhere within the document.

Figure 5. An example of using PasteSpecial to link an Excel Range into Word. Notice that the "Sales Volume" label is cut off. A little work on formatting the column width before cutting to the clipboard would remedy the problem.

Figure 6. Double-clicking on the object added by PasteSpecial. The menu changes to incorporate Excel s menu. When the user is finished editing (indicated by clicking off of the spreadsheet), the display returns to looking like Figure 5, along with reflecting any changes.

If you are linking an object, the source object must be saved as a file. By explicitly saving the source document before copying it to the clipboard, the object on the clipboard will present that fact to the application doing the paste, and the clipboard object can supply the original filename. If the object is not saved, no error results when the PasteSpecial method s lLink parameter is .T. but it doesn t get linked, either.

What about Excel s PasteSpecial command? Well, it s similar, but different. It uses most of the same parameters, but they are organized more coherently, as all the icon parameters are grouped together:

oExcel.ActiveSheet.PasteSpecial( [cFormat], [lLink],

[lDisplayAsIcon], [cIconFileName],

[nIconIndex], [cIconLabel] )

The cFormat parameter (which corresponds to the nDataType parameter for Word s method) is a major change from Word s syntax. It expects a string to identify the format of the data on the clipboard, such as "Microsoft Word 8.0 Document Object." Fortunately, this is only necessary if you want to change the format of the object to something other than what it already is, like changing it to a bitmap or any of the other supported formats. By default, lLink is false, which embeds the object. The remaining parameters are for the icon, and work just like in Word.

How about PowerPoint s PasteSpecial command? Surprisingly, there isn t one. Since the Paste method adds Word text as a Shape object with text, and adds an Excel spreadsheet as an embedded object anyway, we guess Microsoft felt it wasn t needed. But how does one add a linked object to PowerPoint? There is an alternative method to PasteSpecial, which does not require the use of the clipboard. It is the AddOLEObject method, and it s available in all Office servers.

Using AddOLEObject

Why would you use AddOLEObject, when PasteSpecial is available? There are a couple of reasons. First, if you re using PowerPoint, it s the only way to link an object (that s a pretty compelling reason). Second, AddOLEObject accesses a file, which doesn t have to be opened in your code (it can even be a user-generated file). You don t have to select the object and use the clipboard to transfer it.

We re going to use a different example here. Since PowerPoint doesn t have a PasteSpecial method to link to a file, we ll use PowerPoint as our target application. We ll use an Excel Chart as our source data. Charts are visual analysis tools and are very well suited to presentations. It s understandable that someone would want to come up with an automated method to generate PowerPoint slides with charts that reflect the most current data.

PowerPoint s AddOLEObject method is available only to the Shapes collection object. Its syntax is:

oSlide.Shapes.AddOLEObject([nLeft], [nTop], [nWidth], [nHeight],

[cClassName], [cFileName], [lDisplayAsIcon],

[cIconFileName], [nIconIndex], [cIconLabel],

[lLink])

The first two parameters are the position of the left and top edges of the object. These are in points, and the default is 0. The next two parameters define the width and height of the object, in points. Fortunately, these are optional in PowerPoint 2000 (they are not optional in PowerPoint 97).

You pass either cClassName or cFileName, leaving the other blank. Unless you re building blank objects, you won t need to pass a class name or ProgID as cClassName. More likely, you ll pass the filename containing the object to add.

The next four parameters (lDisplayAsIcon, cIconFileName, nIconIndex, and cIconLabel) determine whether the object is displayed as an icon, and are the same as the PasteSpecial parameters.

The last parameter, lLink, determines whether the object is linked or embedded.

Listing 3, stored as AddOLEObject.PRG in the Developer Download files available at www.hentzenwerke.com, shows building and saving a simple chart in Excel, then opening PowerPoint and linking the chart.

Listing 3. Using AddOLEObject to link an Excel chart into a PowerPoint presentation.

* Clean out any existing references to servers.

* This prevents memory loss to leftover instances.

RELEASE ALL LIKE o*

#DEFINE xlColumn 3

#DEFINE xlColumns 2

#DEFINE autoColumnFormat 4

#DEFINE autoOneSeriesLabel 1

#DEFINE autoOneCategoryLabel 1

#DEFINE autoHasLegend .T.

#DEFINE autoIn2Pt 72

#DEFINE ppLayoutBlank 12

* Add a workbook, using default settings

oExcel = CreateObject("Excel.Application")

oExcel.Visible = .T.

oBook = oExcel.Workbooks.Add()

* Add simple data to graph

WITH oBook.Sheets[1]

.Range("A2").Value = "First"

.Range("A3").Value = "Second"

.Range("B1").Value = "Test"

.Range("B2").Value = "10"

.Range("B3").Value = "20"

oChartSource = .Range("A1:B3")

ENDWITH

* Create the chart as a Chart Sheet

oChart = oExcel.Charts.ADD()

oChart.ACTIVATE()

oExcel.ActiveChart.ChartWizard(oChartSource, xlColumn, autoColumnFormat, ;

xlColumns, autoOneCategoryLabel, autoOneSeriesLabel, autoHasLegend, ;

"Sample Chart", "", "")

* Save the file, with the data sheet active

oExcel.Sheets["Sheet1"].Activate()

XLSFile = FULLPATH("SampleChart.XLS")

oExcel.ActiveWorkbook.SaveAs(XLSFile)

oExcel.Quit()

RELEASE oExcel, oBook, oChart, oChartSource

* Make variables for demonstration purposes, so

* you can inspect them after the program finishes

PUBLIC oPowerPoint, oOLEChart

* Create the PowerPoint object

oPowerPoint = CreateObject("PowerPoint.Application")

* Make it visible, and add the presentation and slides

oPowerPoint.Visible = .T.

oPresentation = oPowerPoint.Presentations.Add()

oSlide = oPresentation.Slides.Add(1, ppLayoutBlank)

* Add the worksheet

oOLEChart = oSlide.Shapes.AddOLEObject(;

1.0 * autoIn2Pt, 1.5 * autoIn2Pt,;

8.0 * autoIn2Pt, 5.0 * autoIn2Pt, ,;

XLSFile, ,,,, .T.)

There s a little gotcha with adding Excel charts (and worksheets, too), because there are multiple worksheets in the workbook. How does PowerPoint know which worksheet to display? It displays the first worksheet. If your workbook is set up to have the chart on a sheet other than the first sheet, or if your workbook contains multiple chart sheets, then you can specify which sheet is displayed if the object is linked (this option is not available if the object is embedded). Use the Shape s LinkFormat object s SourceFullName property to specify the sheet name. Set this property to a concatenation of the filename, an exclamation point, and the sheet name, as in "C:\My Files\SampleChart.XLS!Chart1." Once this property is set, update the link by calling the Update method of the Shape s LinkFormat object.

The Excel and Word AddOLEObject methods work similarly, except their list of parameters is in a different order.

oObj.AddOLEObject( [cClassName], [cFileName], [lLink],

[lDisplayAsIcon], [cIconFileName], [nIconIndex],

[cIconLabel], [nLeft], [nTop], [nWidth], [nHeight] )

oObj must be one of the following: an Excel Sheet s Shapes collection object, a Word Document s Shapes collection object, or a Word Document s InlineShapes collection object.

As with PowerPoint, pass either cClassName or cFileName, and leave the other blank. The next parameter is the logical parameter determining whether to link or embed the object (it defaults to false to embed the object). Following that are the icon parameters. Last are the parameters defining the new object s location, in points. If they re not specified, Left and Top default to 0, and the height and width default to the dimensions of the object.

Adding a document to Excel, using syntax like:

oOLEDoc = oSheet.Shapes.AddOLEObject(, "C:\MyDocs\Test.DOC")

adds a document at 0,0 relative to the active cell. If cell B3 is selected, then the new object is placed relative to cell B3. The default size of the object is 468 points wide and 717.75 points high. On the spreadsheet, this takes approximately 10 columns by 57 rows (given the default column width and row height). The size calculates to 6.5 by 9.9 inches, which are the dimensions of the margins stored in the document. If the user double-clicks on the object, it activates in-place editing of the document.

Adding a presentation to Excel, using syntax like:

oOLEPpt = oSheet.Shapes.AddOLEObject(, "C:\MyDocs\Test.PPT")

adds the presentation at 0,0 relative to the active cell. The default size of a PowerPoint presentation is 360 points wide by 270 points high (7 columns by 22 rows). Double-clicking the object runs the slide show.

Add a Presentation to Word at the top, left corner within the margin of the currently selected page with syntax similar to the following:

oOLEPpt = oDoc.Shapes.AddOLEObject(,"C:\MyDocs\Test.PPT")

It adds a presentation that is 360 points wide by 270 points high, and runs the slide show when double-clicked by the user.

Add a workbook to Word at the top, left corner of the current page with a command similar to the following:

oOLEXls = oDoc.Shapes.AddOLEObject(,"C:\MyDocs\Test.XLS")

An Excel worksheet defaults to displaying only the first cell of the worksheet that was active when the file was last saved, regardless of how many cells are used in the worksheet (though we have seen differences, which may be related to page setup properties stored as Excel defaults). The default cell is 52.2 points wide by 13.8 points tall not very big. However, if a chart sheet was the active sheet when the file was saved, then the chart comes in at a whopping 683 by 487 points 9.5 by 6.7 inches, too wide for the page! For Excel worksheets, it seems prudent to specify the height and width, and perhaps predetermine what s in the worksheet (either a chart or the extents of the cells in the worksheet) before adding the object.

In Word, when the object is linked, there is a LinkFormat object with a SourceFullName property, but it does not allow you to specify the sheet name, as PowerPoint does. Word appears to display only the first slide or worksheet, so consider this when designing your application.

Communicating between Office applications, whatever the method, can add significant functionality to your application. Wait until you see what we can do with events.

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