6.4 Putting it all together | Microsoft Office Automation with Visual FoxPro

Putting it all together

To demonstrate this chapter s main lesson, we have a two-part process. Listing 5 shows a program (WordSample3Pt1.PRG in the Developer Download files available at www.hentzenwerke.com) that creates a template for product information sheets for Tasmanian Traders. The template is a mail merge main document attached to a header source only. The program runs a query that collects the data needed (in a real application, you d probably have a view for this data), then calls on CreateHeaderSource.PRG (see Listing 2) to attach the header. It then populates and saves the template. Figure 10 shows the completed template.

Listing 5. Creating a mail merge template. This program generates both a header source and a main document, in this case, a template for a main document.
* Create a main document for product sheets.

* The document is created as a template so that it can

* then be used with File|New.

#DEFINE CR CHR(13)

#DEFINE TAB CHR(9)

#DEFINE wdHeaderFooterPrimary 1

#DEFINE wdGray25 16

#DEFINE wdAlignParagraphCenter 1

#DEFINE wdCollapseEnd 0

#DEFINE wdParagraph 4

#DEFINE wdWord 2

#DEFINE wdLineStyleDouble 7

#DEFINE wdUserTemplatesPath 2

#DEFINE wdGoToBookmark -1

LOCAL oWord, oDocument, oRange, oBorderRange, cTemplatePath

* Open Word and create a new template document

oWord = CreateObject("Word.Application")

oWord.Visible = .T.

oDocument = oWord.Documents.Add(, .T.)

* Create a cursor of all products

OPEN DATABASE _SAMPLES + "TasTrade\Data\TasTrade"

SELECT product_name, english_name, category_name, ;

quantity_in_unit, unit_price, ;

company_name, contact_name, contact_title, ;

address, city, region, postal_code, country, ;

phone, fax ;

FROM products ;

JOIN supplier ;

ON products.supplier_id = supplier.supplier_id ;

JOIN category ;

ON products.category_id = category.category_id ;

ORDER BY Category_Name, Product_Name ;

INTO CURSOR ProductList

* Attach a header source to the template document

DO CreateHeaderSource WITH oDocument, DBF("ProductList"), ;

AddBs(SYS(2023)) +"ProdHeader.DOC"

USE IN ProductList

* Now set up the product sheet

* First, assign a font for Normal

WITH oDocument.Styles["Normal"].Font

.Name = "Times New Roman"

.Size = 12

ENDWITH

* Add a header

WITH oDocument.Sections[1].Headers[ wdHeaderFooterPrimary ]

oRange = .Range()

WITH oRange

.Text = "Tasmanian Traders"

.Style = oDocument.Styles[ "Heading 1" ]

.ParagraphFormat.Alignment = wdAlignParagraphCenter

.Shading.BackgroundPatternColorIndex = wdGray25

ENDWITH

ENDWITH

* Page heading

oRange = oDocument.Range(0,0)

WITH oRange

.Style = oDocument.Styles[ "Heading 2" ]

.ParagraphFormat.Alignment = wdAlignParagraphCenter

.InsertAfter( "Product Information" + CR + CR )

.Collapse( wdCollapseEnd )

* First, add fixed text and set up bookmarks where we want

* the merge fields to go.

* Add Product Category

.Style = oDocument.Styles[ "Heading 3" ]

.InsertAfter( "Product Category: " )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "ProductCategory", oRange )

.InsertAfter( CR )

.Expand( wdParagraph )

.Borders.OutsideLineStyle = wdLineStyleDouble

.Collapse( wdCollapseEnd )

.InsertAfter( CR )

* Add Product Name

.InsertAfter( "Product Name: " )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "ProductName", oRange )

.Collapse( wdCollapseEnd )

.InsertAfter( CR )

oBorderRange = oRange.Paragraphs[1].Range()

.InsertAfter( "English Name: " )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "EnglishName", oRange )

.InsertAfter( CR )

.Collapse( wdCollapseEnd )

oBorderRange.MoveEnd( wdParagraph, 1)

oBorderRange.Borders.OutsideLineStyle = wdLineStyleDouble

* Now units and price

.Style = oDocument.Styles[ "Normal" ]

.InsertAfter( CR + "Sold in units of: " )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "Quantity", oRange )

.InsertAfter( " at a price of: " )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "UnitPrice", oRange )

.InsertAfter( " per unit." + CR + CR )

.Collapse( wdCollapseEnd )

* Now supplier information

* To make things line up, we'll need a tab, so set it up.

WITH oDocument.Paragraphs.TabStops

.ClearAll()

.Add( oWord.InchesToPoints( 1 ) )

ENDWITH

.InsertAfter( "Supplier: " + TAB )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "CompanyName", oRange)

.InsertAfter( CR + TAB)

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "Address", oRange )

.InsertAfter( CR + TAB )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "City", oRange )

.InsertAfter( CR + TAB )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "Region", oRange )

.InsertAfter( CR + TAB )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "PostalCode", oRange )

.InsertAfter( CR + TAB )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "Country", oRange )

.InsertAfter( CR )

.InsertAfter( "Contact: " + TAB )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "ContactName", oRange )

.InsertAfter( CR + TAB)

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "ContactTitle", oRange )

.InsertAfter( CR )

.InsertAfter( "Phone: " + TAB )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "Phone", oRange )

.InsertAfter( CR )

.InsertAfter( "Fax: " + TAB )

.Collapse( wdCollapseEnd )

oDocument.Bookmarks.Add( "Fax", oRange )

.InsertAfter( CR )

* Now insert a mail merge field at each bookmark

oRange = oDocument.Bookmarks[ "ProductCategory" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Category_Name" )

oRange = oDocument.Bookmarks[ "ProductName" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Product_Name" )

oRange = oDocument.Bookmarks[ "EnglishName" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "English_Name" )

oRange = oDocument.Bookmarks[ "Quantity" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Quantity_In_Unit" )

oRange = oDocument.Bookmarks[ "UnitPrice" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Unit_Price" )

oRange = oDocument.Bookmarks[ "CompanyName" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Company_Name" )

oRange = oDocument.Bookmarks[ "Address" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Address" )

oRange = oDocument.Bookmarks[ "City" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "City" )

oRange = oDocument.Bookmarks[ "Region" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Region" )

oRange = oDocument.Bookmarks[ "PostalCode" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Postal_Code" )

oRange = oDocument.Bookmarks[ "Country" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Country" )

oRange = oDocument.Bookmarks[ "ContactName" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Contact_Name" )

oRange = oDocument.Bookmarks[ "ContactTitle" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Contact_Title" )

oRange = oDocument.Bookmarks[ "Phone" ].Range()

oDocument.MailMerge.Fields.Add( oRange,"Phone" )

oRange = oDocument.Bookmarks[ "Fax" ].Range()

oDocument.MailMerge.Fields.Add( oRange, "Fax" )

ENDWITH

cTemplatePath = oWord.Options.DefaultFilePath( wdUserTemplatesPath )

oDocument.SaveAs( AddBs(cTemplatePath) + "ProdInfo")

RETURN

Figure 10. Creating mail merge documents. This template was created by Listing 5. It has a header source and is based on a query from the TasTrade database.

The second part of the process is to create an actual data source when you re ready to perform the mail merge. Listing 6 shows the code (WordSample3Pt2.PRG in the Developer Download files available at www.hentzenwerke.com) that creates the new document from the template, calls on CreateDataSource.PRG (see Listing 3), then performs the merge and shows the result. Figure 11 shows part of the result.

Listing 6. Performing a merge. This code uses the template created by Listing 5 to generate a new document, creates a data source, and executes the merge.
* Create the Product Information sheets based on the

* template, using mail merge

#DEFINE wdUserTemplatesPath 2

#DEFINE wdWindowStateMaximize 1

LOCAL cTemplatePath, oDocument, oMergedDocument

* Create an instance of Word.

* Make it public for demonstration purposes.

RELEASE ALL LIKE o*

PUBLIC oWord

oWord = CreateObject("Word.Application")

* Make Word visible.

oWord.Visible = .t.

* Create a new document based on the template

cTemplatePath = oWord.Options.DefaultFilePath( wdUserTemplatesPath )

oDocument = oWord.Documents.Add( AddBs(cTemplatePath) + "ProdInfo" )

* Run the query to create a cursor of all products

* Create a cursor of all products

OPEN DATABASE _SAMPLES + "TasTrade\Data\TasTrade"

SELECT product_name, english_name, category_name, ;

quantity_in_unit, TRANSFORM(unit_price, "@$") AS unit_price, ;

company_name, contact_name, contact_title, ;

address, city, region, postal_code, country, ;

phone, fax ;

FROM products ;

JOIN supplier ;

ON products.supplier_id = supplier.supplier_id ;

JOIN category ;

ON products.category_id = category.category_id ;

ORDER BY Category_Name, Product_Name ;

INTO CURSOR ProductList

* Now create and attach a data source

DO CreateDataSource WITH oDocument, DBF("ProductList"), ;

AddBs(SYS(2023)) + "ProdData"

USE IN ProductList

* Perform the mail merge

oDocument.MailMerge.Execute()

oMergedDocument = oWord.ActiveDocument

WITH oMergedDocument

IF .ActiveWindow.WindowState <> wdWindowStateMaximize

* Move it to make it visible - for some reason, it comes up

* way off screen

.ActiveWindow.Left = 0

.ActiveWindow.Top = 0

ENDIF

* Preview it

.PrintPreview()

ENDWITH

RETURN

Figure 11. Mail merge results. This is the product information sheet created by the programs in Listings 5 and 6. There s one sheet for each product.

We d like to say, "That s all, folks!" But the truth is that, even with three full chapters devoted to it, there s far more to Word than we ve been able to cover here. So, if there s something you want to do in a document and we haven t shown you the way, just dig in and try it. Remember the keys to figuring it out. First, try to do it interactively. Try recording a macro. Use the Word VBA Help file and the Object Browser to find out what objects, methods, and properties are involved. Word s object model is extremely rich. If you can imagine it, it can probably be done.