[QA info]

Hi, I'm trying to update our current forms, which have text form fields so
that staff can easily type in information gathered from clients they speak to
on the phone. But I'd like to partially fill in the forms with mail merge
before assigning them to the verifiers. Currently, when I do a test merge,
the text form fields are replaced by regular white space. Is it possible to
maintain the text form field capability, or is it only possible to add this
formatting back in after completing the merge?

[Doug Robbins - Word MVP on news.microsoft.com]

Here is a macro that can be used to do a mail merge with formfields if the
datasource is a table in an Access Database. The mail merge main document
must be set up with the data source attached and the merge fields inserted
into the document. It can be used with a document that contains any type of
formfield and creates a separate document for each record in the data source
with a filename of the format MwithFF# in a folder c:\Test (that must be
created on your system). You can change the MwithFF to something else if
you want and also the folder into which the documents are saved by modifying
the

..SaveAs "C:\Test\MwithFF" & j

line of code.

Sub MailMergewithFormFields()
Dim dSource As String
Dim qryStr As String
Dim mfCode As Range
Dim i As Long, j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
End With
'Convert the MERGEFIELDS to DOCVARIABLE fields
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
mfCode = Replace(mfCode, "MERGEFIELD", "DOCVARIABLE")
End If
Next i
'Convert the Mail Merge Main document to a normal Word document
.MailMerge.MainDocumentType = wdNotAMergeDocument
.Protect wdAllowOnlyFormFields, NoReset
End With
' Open the database
Set db = OpenDatabase(dSource)
' Retrieve the recordset
Set rs = db.OpenRecordset(qryStr)
With rs
' Move to the first record
.MoveFirst
j = 1
Do While Not .EOF
'Create variables in the document with the names and values of the
fields in each record
For i = 0 To .Fields.Count - 1
If .Fields(i).Value "" Then
ActiveDocument.Variables(.Fields(i).Name).Value =
..Fields(i).Value
End If
Next i
With ActiveDocument
.Fields.Update
.SaveAs "C:\Test\MwithFF" & j
End With
.MoveNext
j = j + 1
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub


If you data source is not in that format, the following macro can be used to
execute the merge to a new document in which the formfields that were in the
mailmerge main document, will be reinstated into each of the "letters"
contained in that new document:

Sub MergewithFormFields()

Dim i As Long

With ActiveDocument

For i = .FormFields.Count To 1 Step -1

If .FormFields(i).Type = wdFieldFormTextInput Then

.FormFields(i).Range.Text = "FF" & i

End If

Next i

With .MailMerge

.Destination = wdSendToNewDocument

.Execute

End With

End With

Selection.HomeKey wdStory

With Selection.Find

Do While .Execute(FindText:="FF[0-9]{1,}", Forward:=True, _

MatchWildcards:=True, Wrap:=wdFindStop, MatchCase:=True) = True

ActiveDocument.FormFields.Add Selection.Range, wdFieldFormTextInput

Loop

End With

End Sub




--
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, originally posted via msnews.microsoft.com

"QA info" QA wrote in message
news
Hi, I'm trying to update our current forms, which have text form fields so
that staff can easily type in information gathered from clients they speak
to
on the phone. But I'd like to partially fill in the forms with mail merge
before assigning them to the verifiers. Currently, when I do a test merge,
the text form fields are replaced by regular white space. Is it possible
to
maintain the text form field capability, or is it only possible to add
this
formatting back in after completing the merge?

[QA info[_2_]]

Great! It's a relief to get help from someone who understands what I'm trying
to do.

I will look over this and see if I can get it to make sense. I think I can
figure out how to record a macro. The datasource is currently blank, it would
be created each day from the orders that we receive. The default datasource
that Word wants to use is an Access Database, so that's what I've been trying
to learn, unless there is something else that will make more sense.

"Doug Robbins - Word MVP on news.microsof" wrote:

Here is a macro that can be used to do a mail merge with formfields if the
datasource is a table in an Access Database. The mail merge main document
must be set up with the data source attached and the merge fields inserted
into the document. It can be used with a document that contains any type of
formfield and creates a separate document for each record in the data source
with a filename of the format MwithFF# in a folder c:\Test (that must be
created on your system). You can change the MwithFF to something else if
you want and also the folder into which the documents are saved by modifying
the

..SaveAs "C:\Test\MwithFF" & j
line of code.

If you data source is not in that format, the following macro can be used to
execute the merge to a new document in which the formfields that were in the
mailmerge main document, will be reinstated into each of the "letters"
contained in that new document:

[Doug Robbins - Word MVP on news.microsoft.com]

If by "The default datasource that Word wants to use is an Access Database",
you mean the file that is created when you Type a New List in the Word Mail
Merge facility, the code that I posted will not work with that type of .mdb
file. It only works with a table that is created in a database that is
created in Microsoft Access. I should also have indicated that it is
necessary in the Visual Basic Editor to set a reference under
ToolsReferences to the Micorosft DAO 3.6 (or 3.5) Object library.

Here is a version of the macro that will work with a data source that is in
an Excel Spreadsheet. Once again, it requires the are reference be set to
the DAO object library:

Sub MailMergewithFormFields()
Dim dSource As String
Dim qryStr As String
Dim mfCode As Range
Dim i As Long, j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
End With
'Convert the MERGEFIELDS to DOCVARIABLE fields
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
mfCode = Replace(mfCode, "MERGEFIELD", "DOCVARIABLE")
End If
Next i
'Convert the Mail Merge Main document to a normal Word document
.MailMerge.MainDocumentType = wdNotAMergeDocument
.Protect wdAllowOnlyFormFields, NoReset
End With
' Open the database
Set db = OpenDatabase(dSource, False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset(qryStr)
With rs
' Move to the first record
.MoveFirst
j = 1
Do While Not .EOF
'Create variables in the document with the names and values of the
fields in each record
For i = 0 To .Fields.Count - 1
If .Fields(i).Value "" Then
ActiveDocument.Variables(.Fields(i).Name).Value =
..Fields(i).Value
End If
Next i
With ActiveDocument
.Fields.Update
.SaveAs "C:\Test\MwithFF" & j
End With
.MoveNext
j = j + 1
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub


--
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, originally posted via msnews.microsoft.com

"QA info" wrote in message
...
Great! It's a relief to get help from someone who understands what I'm
trying
to do.

I will look over this and see if I can get it to make sense. I think I can
figure out how to record a macro. The datasource is currently blank, it
would
be created each day from the orders that we receive. The default
datasource
that Word wants to use is an Access Database, so that's what I've been
trying
to learn, unless there is something else that will make more sense.

"Doug Robbins - Word MVP on news.microsof" wrote:

Here is a macro that can be used to do a mail merge with formfields if
the
datasource is a table in an Access Database. The mail merge main
document
must be set up with the data source attached and the merge fields
inserted
into the document. It can be used with a document that contains any type
of
formfield and creates a separate document for each record in the data
source
with a filename of the format MwithFF# in a folder c:\Test (that must be
created on your system). You can change the MwithFF to something else if
you want and also the folder into which the documents are saved by
modifying
the

..SaveAs "C:\Test\MwithFF" & j
line of code.

If you data source is not in that format, the following macro can be used
to
execute the merge to a new document in which the formfields that were in
the
mailmerge main document, will be reinstated into each of the "letters"
contained in that new document:

[QA info[_2_]]

Sorry, I'm having trouble with this step. I got the macro to work as an
Access Database, but I've decided it will be easier as an excel file, and I
am having difficulty setting this reference to what's required.

This is probably a stupid question, but does the data source need to be
straight across a row? I'd like my data to come out of a column instead if
possible.

"Doug Robbins - Word MVP on news.microsof" wrote:

I should also have indicated that it is
necessary in the Visual Basic Editor to set a reference under
ToolsReferences to the Micorosft DAO 3.6 (or 3.5) Object library.