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.