This method uses the Windows clipboard to transfer data to Excel again utilizing dBASE Automation. You'll need a clipbord.cc and StringEx.cc which are included in this zip file. We'll dispense with a lot of the comments and error trapping from the previous example 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.
[MS Word Mail-Merge.how] [Data Source Word Doc] [Data Source dBASE Table]
Last modified: November 9, 1999