Creating the Data Source in Word
by Gary White, dBVIPS

OKAY, now let's look at a mail-merge operation. For this example, we'll use the customer table from the Mugs sample database. We'll create a short letter and address it to all the customers in the state of California. We'll create both the document and the mail-merge data source from scratch, right from within dBASE.

Educational Tip: There will be those who feel that the salutation of this letter, using the customer's first name, is a bit too casual for a business letter. I agree. I hope this brings up the point that a preferred greeting (Mr. Ms. Dr., etc.) is very important. Remember this next time you design a table.

| `// the dsName is the path & file name of our merge data source` `// you'll need to change this if you don't have a C:\My Documents folder.` `#define dsName "MergeSrc.doc"` `// next, define the fields used in the merge. this is a simple string where` `// the individual fields are separated using the character specified in the` `// control panel "Regional Settings" as a "List Separator". In most cases it` `// is a comma.` `#define dsFields "FName,LName,Address1,Address2,City,State,PostalCode"` `// this will be the body of the letter.` `cBody = "Acme Widgets is happy to announce a special promotion, available " + ;` `"only within the state of California. For a limited time only, our " + ;` `"California clients may purchase the new SuperWidget, with California " + ;` `"emission controls, for only $42,386,941.22. We hope you will take " + ;` `"advantage of this special sale right away."` `try` `set database to` `if not file('customer.dbf')` `create table 'customer.dbf'(;` `customer.'First Name' char(15),;` `customer.'Last Name' char(15),;` `customer.'Street1' char(25),;` `customer.'Street2' char(25),;` `customer.'City' char(20),;` `customer.'State ID' char(2),;` `customer.'Postal' char(10))` `insert into customer values (;` `'Sarah', 'Connor', '4567 Juniper', '', 'Hertown', 'CA', '99999')` `insert into customer values (;` `'Frank', 'Brown', '4321 Maple', '', 'Yourtown', 'NY', '11111')` `insert into customer values (;` `'John', 'Smith', '1234 Main St', '', 'Mytown', 'KS', '66666')` `insert into customer values (;` `'Bill', 'Williams', '5555 Walnut', '', 'Histown', 'CA', '99999')` `endif` `// begin by trying to get our data for the merge source` `q = new query()` `q.sql := 'select * from customer where customer."State Id"="CA"'` `q.active := true` `q.rowset.first()` `// if we found any records to process` `if not q.rowset.endOfSet` `// get a shortened reference to our fields array` `f = q.rowset.fields` `// try to start MS Word` `oWord = new oleautoclient('word.application')` `// create a new document` `oDoc = oWord.documents.add()` `// save a reference to this doc so we can come back to it later` `n = oWord.activedocument.name` `// make it a form letter` `oWord.activeDocument.MailMerge.MainDocumentType = 0` `// get a reference to the MailMerge object` `oMerge = oDoc.mailmerge` `// next, we create the datasource using the values #define'd above` `oMerge.CreateDataSource( dsName, "", "", dsFields )` `// now, we'll open the datasource so we can insert data` `oMerge.editDataSource()` `// get a reference to the document` `ds = oWord.activedocument` `// the datasource will be a document with a table containing the field` `// names in the first row and an empty second row` `// start by getting a reference to the table` `t = ds.tables(1)` `if q.rowset.first()` `// now just loop through the data and put it in the table` `do while not q.rowset.endOfSet` `// the last row of the table is empty, so we can use the count property` `// of the rows collection of the table to select the row` `r = t.rows(t.rows.count)` `c = r.cells(1)` `c.range.insertAfter( trim( f['First Name'].value ) )` `c = r.cells(2)` `c.range.insertAfter( trim( f['Last Name'].value ) )` `c = r.cells(3)` `c.range.insertAfter( trim( f['Street1'].value ) )` `c = r.cells(4)` `c.range.insertAfter( trim( f['Street2'].value ) )` `c = r.cells(5)` `c.range.insertAfter( trim( f['City'].value ) )` `c = r.cells(6)` `c.range.insertAfter( trim( f['State ID'].value ) )` `c = r.cells(7)` `c.range.insertAfter( trim( f['Postal'].value ) )` `// get the next record` `q.rowset.next()` `// if we're not done yet, add another row to the table` `if not q.rowset.endOfSet` `t.rows.add()` `endif` `enddo` `endif` `// now activate the mail merge document` `oWord.documents(n).activate()` `// get a little shorter reference to save some typing` `oDoc = oWord.activeDocument` `// center the date at the top of the page` `oWord.selection.ParagraphFormat.Alignment := 1` `oWord.selection.InsertDateTime("MMMM d, yyyy",true)` `oWord.selection.TypeParagraph()` `// set alignment back to "left aligned"` `oWord.selection.ParagraphFormat.Alignment := 0` `for i = 1 to 3 // insert three blank lines` `oWord.selection.TypeParagraph()` `endfor` `// now we insert our merge fields` `oDoc.mailMerge.Fields.add(oWord.selection.range, "FName")` `oWord.selection.TypeText(" ") // space between first and last name` `oDoc.mailMerge.Fields.add(oWord.selection.range, "LName")` `oWord.selection.TypeParagraph() // new line` `oDoc.mailMerge.Fields.add(oWord.selection.range, "Address1")` `oWord.selection.TypeParagraph() // new line` `oDoc.mailMerge.Fields.add(oWord.selection.range, "Address2")` `oWord.selection.TypeParagraph() // new line` `oDoc.mailMerge.Fields.add(oWord.selection.range, "City")` `oWord.selection.TypeText(", ")` `oDoc.mailMerge.Fields.add(oWord.selection.range, "State")` `oWord.selection.TypeText(" ")` `oDoc.mailMerge.Fields.add(oWord.selection.range, "PostalCode")` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeText("Dear ")` `oDoc.mailMerge.Fields.add(oWord.selection.range, "FName")` `oWord.selection.TypeText(",")` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeText( cBody ) // insert the body of the letter` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeText("Sincerely,") // letter closing` `for i = 1 to 4 // insert four blank lines` `oWord.selection.TypeParagraph()` `endfor` `oWord.selection.TypeText("John Smith")` `// now set the destination per the following:` `// 0 = new document` `// 1 = printer` `// 2 = e-mail` `// 3 = fax` `oMerge.destination := 0` `// now perform the merge` `oMerge.execute(false)` `// show the result` `oWord.visible = true` `endif` `catch (exception e)` `clear` `? "Error number",e.code` `? e.message` `? "occurred on line",e.lineNo` `finally` `if type('q') == 'O'` `q.active = false` `release object q` `endif` `release q` `endtry`
| ``

Now, if you run the above code, you'll find it to be everything but fast. While it may be possible to speed this up, I've not had a lot of success in doing so. However, there is a way to move data into Excel that is very fast and Word is perfectly happy with an Excel spreadsheet as a data source for a merge.