[Nancy]

I have XP Pro with Word. I need to create a form that will draw the data from
an excel sheet. But if the cell in the data is empty, after the merge I will
need a salesman to fill it in. If I do a form and a merge, the form fields
disappear on the merged docs. Also, I would like my form to be protected
except for the necessary fields. Any ideas? I have been working on it all
week!

[Doug Robbins - Word MVP]

Mail merge and formfields are not compatible and it is also not possible to
mailmerge into a protected document.

The alternative is to create a "roll-your-own" equivalent to mailmerge that
uses a template containing document variable fields, and in your case Text
FormFields in place of merge fields and have vba code iterate through the
data source, creating a new document from the template for each record and
setting the values of variables in the document to the corresponding values
from the data source, and in the case of those data items that may be blank
and that you would want someone to later fill in, having the code set the
..Result property of the formfield that you would have in the template for
such items.

--
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

"Nancy" wrote in message
...
I have XP Pro with Word. I need to create a form that will draw the data
from
an excel sheet. But if the cell in the data is empty, after the merge I
will
need a salesman to fill it in. If I do a form and a merge, the form
fields
disappear on the merged docs. Also, I would like my form to be protected
except for the necessary fields. Any ideas? I have been working on it all
week!

[Nancy]

Thank you. In other words, I would have to be able to enter vba code into the
Excel sheet which is my data source? Is there somewhere you can direct me to
for more information on this? Thank you very much!

"Doug Robbins - Word MVP" wrote:

Mail merge and formfields are not compatible and it is also not possible to
mailmerge into a protected document.

The alternative is to create a "roll-your-own" equivalent to mailmerge that
uses a template containing document variable fields, and in your case Text
FormFields in place of merge fields and have vba code iterate through the
data source, creating a new document from the template for each record and
setting the values of variables in the document to the corresponding values
from the data source, and in the case of those data items that may be blank
and that you would want someone to later fill in, having the code set the
..Result property of the formfield that you would have in the template for
such items.

--
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

"Nancy" wrote in message
...
I have XP Pro with Word. I need to create a form that will draw the data
from
an excel sheet. But if the cell in the data is empty, after the merge I
will
need a salesman to fill it in. If I do a form and a merge, the form
fields
disappear on the merged docs. Also, I would like my form to be protected
except for the necessary fields. Any ideas? I have been working on it all
week!

[Doug Robbins - Word MVP]

Hi Nancy,

I guess the macro could reside in the Excel sheet, but I would be inclined
to have it in Word.

The following is for a different purpose and would need modification for
your case, but it does cover the mechanics of getting data from Excel and
using it to set the values of document variables. In your case, you would
need to have the code create a new document for each record in the data
source and create and set the values for as many document variables as there
are fields in the data source.

The following code, written for use with Word 2007 and an Excel 2007 data
source requires:

1. In the Visual Basic Editor - a reference must be set to the Microsoft
Office 12.0 Access database engine Object Library

If using earlier versions of Office, the reference should be set to
the Microsoft DAO 3.6 Object Library and the following line of the code

Set db = OpenDatabase(DataSource, False, False, "Excel 12.0;
IMEX=1;")

must be replaced with

Set db = OpenDatabase(DataSource, False, False, "Excel 8.0;
IMEX=1;")

I do not know the equivalent of these setup steps with a Mac. If you
need assistance with that aspect of it, I would suggest that you post to one
of the Microsoft.Public.Mac.Office newsgroups.

2. In the Excel spreadsheet that is selected when the code is run, there
is a two column list with "field names" in the first column and the
corresponding values in the second column. It assumes that the first row is
a header row - probably containing the column titles "field name" and
"value" (but what is actually in the cells of that row does not really
matter. The list, including the header row must be named "DataItems"

When the code runs, it will create a document variable for each record in
the datasource with the name of the variable being the item in the first
column of the spreadsheet and the value of the variable being the item in
the second column of the spreadsheet.

To facilitate the insertion of the Docvariable fields in the document, I
would create a userform with a listbox that was populated with the field
names from the first column of the spreadsheet and a button that when
clicked would insert a docvariable field into the documnet for the field
that was selected in the listbox. It would in fact be possible to construct
a user interface for this that had virtually the same functionality as the
mail merge interface.

Dim Msg, Style, Title, Response
Dim i As Long
Dim j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varname As String
Dim varvalue As String
Dim fd As FileDialog
Dim DataSource As String
Dim avar As Variable
For Each avar In ActiveDocument.Variables
avar.Value = " "
Next avar
'Display the file selection dialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Select Data Source"
.InitialFileName = ""
If .Show = -1 Then
DataSource = .SelectedItems(1)
Else
Exit Sub
End If
End With
Set fd = Nothing
Msg = "Please ensure that Excel is closed before Continuing. Do you want to
continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Refresh Data" ' Define title.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
' Open the file containing the Data
Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;")
Set rs = db.OpenRecordset("SELECT * FROM `DataItems`")
With rs
.MoveFirst
For i = 0 To .RecordCount
ActiveDocument.Variables(Format(.Fields(0).Value)) =
..Fields(1).Value
.MoveNext
Next i
End With
' Cleanup
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Else
Exit Sub
End If


--
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

"Nancy" wrote in message
...
Thank you. In other words, I would have to be able to enter vba code into
the
Excel sheet which is my data source? Is there somewhere you can direct me
to
for more information on this? Thank you very much!

"Doug Robbins - Word MVP" wrote:

Mail merge and formfields are not compatible and it is also not possible
to
mailmerge into a protected document.

The alternative is to create a "roll-your-own" equivalent to mailmerge
that
uses a template containing document variable fields, and in your case
Text
FormFields in place of merge fields and have vba code iterate through the
data source, creating a new document from the template for each record
and
setting the values of variables in the document to the corresponding
values
from the data source, and in the case of those data items that may be
blank
and that you would want someone to later fill in, having the code set the
..Result property of the formfield that you would have in the template
for
such items.

--
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

"Nancy" wrote in message
...
I have XP Pro with Word. I need to create a form that will draw the data
from
an excel sheet. But if the cell in the data is empty, after the merge I
will
need a salesman to fill it in. If I do a form and a merge, the form
fields
disappear on the merged docs. Also, I would like my form to be
protected
except for the necessary fields. Any ideas? I have been working on it
all
week!