Rather than having to have the macro interact with Excel, it will be easier
if you create a Catalog (or in Word XP and later, it is called "Directory")
type mailmerge main document in which you have a one row table into the
cells of which you insert the mergefields from your Excel datasource. When
you execute that merge to a new document, that document will contain a table
with a row of data for each record in the datasource. In the code below, it
is assumed that you save that document as C:\Documents and Settings\[User
Name]\My Documents\My Data Sources\DataDoc.doc").
Now you need to create or save your form as a template and inplace of
mergefields in that template, insert { DOCVARIABLE varname } fields. In the
code below, it is assumed that those fields are named var1, var2, ... varj
where you will have the
{ DOCVARIABLE var1 } field in the location where the data from the first
column of the table in the document that you saved as DataDoc is required
and the { DOCVARIABLE var2 } field in the location where the data from the
second column of the table is required, and so on for each field in the
datasource. Protect the template for Forms and then save and close it and
also the DataDoc document.
Now when you run the following macro, it will create a new document from the
template for each record in the datasource, with the data from each record
in the datasource inserted into it. Each of those documents will be saved
with the filename myformdoc1, myformdoc2, etc, but you can have the newdoc
in the filename replaced by anything else that you might want to use by
inserting whatever that is in the line "newdoc.SaveAs "myformdoc" & i"
in place of the myformdoc.
Dim datasource As Document, newdoc As Document, datarange As Range, i As
Long, j As Long, datatable As Table
Set datasource = Documents.Open("C:\Documents and Settings\[User Name]\My
Documents\My Data _ Sources\DataDoc.doc")
Set datatable = datasource.Tables(1)
For i = 1 To datasource.Tables(1).Rows.Count
Set newdoc = Documents.Add("C:\Documents and Settings\Doug
Robbins\Application _ Data\Microsoft\Templates\DataForm.dot")
For j = 1 To datatable.Columns.Count
Set datarange = datatable.Cell(i, j).Range
datarange.End = datarange.End - 1
newdoc.Variables("varj").Value = datarange.Text
Next j
newdoc.Fields.Update
newdoc.SaveAs "myformdoc" & i
newdoc.Close
Next i
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
