number of fields and mass inserting

[Lauri]

I have two questions:
I am setting up an elementary report card that merges data from an elaborate
Excel 2000 spreadsheet (grade book) into a Word 2000 document (report card).

Is there a limit to the number of merge fields I can use? I think there
are currently 185 columns on the spreadsheet.

Is there a way to mass insert all the merge fields? Picking the fields off
the list is getting extremely tedious. If I could mass insert the fields
and then drag them to where I want them would be so much easier.

Thanks for any and all suggestions.

 

[Doug Robbins]

When there are no more columns available on your spreadsheet you will have
reached the limit.

Using a macro you could insert all of the fields in one go.
--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Lauri]

Could you elaborate on the macro? I'm not sure how to write it so that it
chooses all of the fields, or at least one after the other.

-Lauri

 

[Doug Robbins]

This is not a full automation of the process, but will probably do you.

1. Select the cells in the first row of your Excel spreadsheet that contain
the field names and copy it to the clipboard.

2. Move to an empty area of the spreadsheet or to a new sheet and use
Edit>Paste Special and check the transpose box so that you have a column of
cells containing the field names.

3. Select all of those Cells and copy and paste them into an empty Word
document

4. Select the resulting table in Word and from the Tables menu, select
the Convert table to text item. You now should have a Word document
containing a paragraph for each of the field names, containing the field
names.

5. Now run the following macro to convert the text in each paragraph into
a mergefield with the name of the corresponding field in the Excel spread
sheet.

Dim i As Long, afield As Range
For i = 1 To ActiveDocument.Paragraphs.Count
Set afield = ActiveDocument.Paragraphs(i).Range
afield.End = afield.End - 1
ActiveDocument.Fields.Add Range:=afield, Type:=wdFieldMergeField,
Text:=afield
Next

If you want the mergefields in some other document, you can copy and paste
them there.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP