Creating the Data Source in Excel
by Gary White, dBVIPS

THIS METHOD uses the Windows clipboard to transfer data to Excel again utilizing dBASE Automation. You'll need the `clipbord.cc` which is available in the dUFLP at Ken Mayer's web site. We'll dispense with a lot of the comments and error trapping from above and simply demonstrate how to use an Excel worksheet as a datasource. The one caveat here is Excel's limit of 65536 rows.

| `// Specify a file and path for our Excel document` `#define dsName "C:\My Documents\Temp.xls"` `// The body of our "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." + chr(13) + chr(13) + ;` `"Thank you for taking the time to read this. We, at Acme Widgets, " + ;` `"look forward to hearing from you in the near future."` `try` `// begin by trying to get our data for the merge source` `d = new database()` `d.databaseName := 'MUGS'` `d.active := true` `q = new query()` `q.database := d` `q.sql := 'select customer."First Name",customer."Last Name", ' + ;` `'Street1, Street2, City, customer."State ID", Postal from customer ' + ;` `'where customer."State Id"="CA"'` `q.active := true` `r = q.rowset` `r.first()` `cText = ''` `// place our field names for column headers` `// note that Word does not like spaces in field names` `// so we'll replace the spaces with underscores` `cText +='First_Name' + chr(9) + 'Last_Name' + chr(9) + 'Street1' + ;` `chr(9) + 'Street2' + chr(9) + 'City' + chr(9) + ;` `'State_ID' + chr(9) + 'Postal' + chr(13)` `do while not r.endOfSet` `` `for i = 1 to r.fields.size` `cText += trim( '' + r.fields[i].value ) + chr(9)` `endfor` `// get the next record` `r.next()` `cText += chr(13)` `enddo` `q.active=false` `set procedure to clipbord.cc additive` `_app.Clip = new TextClipboard() // create our clipboard object` `_app.Clip.SetClipboardText( cText ) // place our string` `if file( dsName )` `erase ( dsName )` `endif` `// now we start Excel` `oExcel = new oleAutoclient("excel.application")` `// create a new workbook` `oBook = oExcel.workbooks.add()` `// since the field and record separators are embedded` `// all we need to do is select the top left cell.` `oExcel.ActiveSheet.cells( 1,1 ).select()` `// now just call Excel's paste method` `oExcel.ActiveSheet.paste()` `// save the file` `oBook.SaveAs( dsName )` `// quit Excel` `oExcel.quit()` `release object oExcel` `release oExcel` `// now start Word` `oWord = new oleautoclient('word.application')` `oDoc = oWord.documents.add()` `n = oWord.activedocument.name` `oWord.activeDocument.MailMerge.MainDocumentType = 0` `oWord.ActiveDocument.MailMerge.OpenDataSource( ;` `dsName , 0, false, false, true, ;` `false, '','',false,"", "", "Entire Spreadsheet")` `oWord.documents(n).activate()` `oDoc = oWord.activeDocument` `oWord.selection.ParagraphFormat.Alignment := 1` `oWord.selection.TypeText("Acme Widgets Corporation")` `oWord.selection.TypeParagraph()` `oWord.selection.TypeText("1234 Main Street")` `oWord.selection.TypeParagraph()` `oWord.selection.TypeText("MyTown, AA 12345")` `oWord.selection.TypeParagraph()` `oWord.selection.InsertDateTime("MMMM d, yyyy",true)` `oWord.selection.TypeParagraph()` `oWord.selection.ParagraphFormat.Alignment := 0` `for i = 1 to 3` `oWord.selection.TypeParagraph()` `endfor` `oDoc.mailMerge.Fields.add(oWord.selection.range, "First_Name")` `oWord.selection.TypeText(" ") // space between first and last name` `oDoc.mailMerge.Fields.add(oWord.selection.range, "Last_Name")` `oWord.selection.TypeParagraph() // new line` `oDoc.mailMerge.Fields.add(oWord.selection.range, "Street1")` `oWord.selection.TypeParagraph() // new line` `oDoc.mailMerge.Fields.add(oWord.selection.range, "Street2")` `oWord.selection.TypeParagraph() // new line` `oDoc.mailMerge.Fields.add(oWord.selection.range, "City")` `oWord.selection.TypeText(", ")` `oDoc.mailMerge.Fields.add(oWord.selection.range, "State_ID")` `oWord.selection.TypeText(" ")` `oDoc.mailMerge.Fields.add(oWord.selection.range, "Postal")` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeText("Dear ")` `oDoc.mailMerge.Fields.add(oWord.selection.range, "First_Name")` `oWord.selection.TypeText(",")` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeParagraph() // new line` `oWord.selection.TypeText( cBody )` `oWord.selection.TypeParagraph()` `oWord.selection.TypeParagraph()` `oWord.selection.TypeText("Sincerely,")` `oWord.selection.TypeParagraph()` `oWord.selection.TypeParagraph()` `oWord.selection.TypeParagraph()` `oWord.selection.TypeParagraph()` `oWord.selection.TypeText("John Smith")` `// now, just for grins - make the whole thing` `// Times New Roman font 12pt` `with ( oWord.ActiveDocument.Styles("Normal").Font )` `Name = "Times New Roman"` `Size = 12` `Bold = False` `Italic = False` `endwith` `With ( oWord.ActiveDocument.Styles("Heading 2").Font )` `Name = "Times New Roman"` `Size = 18` `Bold = True` `Italic = True` `endwith` `With ( oWord.ActiveDocument.Styles("Heading 2").ParagraphFormat )` `Alignment = 1 // center` `endwith` `oWord.Selection.GoTo( 3, 1 )` `oWord.Selection.style = oWord.ActiveDocument.Styles("Heading 2")` `// now execute the mail-merge` `oDoc.mailMerge.destination := 0` `oDoc.mailMerge.execute(false)` `// we can now close the mail merge document leaving` `// only the merged results document` `oDoc.close( 0 )` `oWord.visible = true` `catch (exception e)` `if type('oWord') == 'O'` `try` `oWord.quit(0)` `catch ( exception e )` `endtry` `endif` `clear` `? e.code` `? e.message` `? e.lineNo` `finally` `if type("oExcel") == "O"` `try` `oExcel.quit()` `catch ( exception e )` `endtry` `endif` `endtry`
| ``

If you have more than a trivial size mailing list, you'll find this second method to be the fastest way to do your mail merge. Again, the limit being the maximum of 65536 rows.