Insert Merge Field drop-down menu doesn't list all column entries

[RatherBeAtTheBeach]

I want to use an excel spreadsheet with approx 250+ columns as the source
file ("recipient list") for a Word merge doc. However, the "Insert Merge
Field" drop-down menu only lists the first 100 or so entries (column
headings).

I'd just type a list, but I'll need to use repeatedly use that spreadsheet
(converted from downloaded .csv files) when I do the actual merges. Help! How
do I get it to recognize all the fields?
Thanks

 

[Peter Jamieson]

First of all, you cannot use more than 255 columns in an Excel sheet as the
data source for a merge, no matter how you connect to the sheet - even with
Excel 2007, which allows a whole lot more columns per sheet. So if your
column count is set to go up, using Excel as a data source is not going to
work longer term unless you can extract the columns you need /before/ the
merge. Some connection methods may not even let you use 255. As far as I
know, the only data sources that can actually use more than 255 are
delimited text files that Word opens using its internal or external "text
converters" (i.e. because Word tries to open plain text files using an OLE
DB provider in the first instance, you may have to find a way to force it to
use a text converter instead, e.g. open the .csv in Word and save it as a
..doc format file)

Secondly, I have personally encountered limits in the number of columns Word
"sees", but these limits do not appear to be fixed - e.g. as far as I know
in Word 2007 there is no fixed limit in the drop-down of 100 or even 127 or
128. However, there might be such a limit for one of the connection methods
(DDE, say). There are also limits in the number of coulmns, rows and amount
of data that the mail merge recipients dialog box can show, for example.

Generally speaking I have found that if Word does not list a field in its
drop-down it's because it is not "seeing" that field. However, you can
verify that that is or is not the case by inserting the appropriate merge
field manually and seeing if Word populates it - e.g. suppose the last
column in your sheet is called mylastcoulumn, insert the field

{ MERGEFIELD mylastfield }

in your document and see if Word populates it during preview (and during a
merge, preferably). If Word doesn't recognise it, it's not just a dropdown
display problem.

Other things might conceivably cause this problem, e.g. if you have a column
with a blank name and data somewhere after 100 columns or so.

 

[RatherBeAtTheBeach]

Thank you, Peter, much appreciated. I'll jettison Excel and just use a .csv
or .txt file.

Toward that end, I've run into another problem. It may be a bit off subject
for this forum as it's one step back in the process, but if you could point
me in the right direction, I appreciate it.

Here's the bigger picture: My clients send me a large amount of information
which I then use to create promotional items for them. I'm trying to create
forms they can fill in that I can then extract the data from into a .txt or
..csv file; which will, in turn, be the source file for for the various merged
fields in the templated documents which I then re-write/edit.

Forgive me for asking an elementary question, but I just upgraded to 2007
and after scouring "Help," can still find nothing that tells me how to
extract data from a filled-in form and send it into a .txt/.csv file?

If you have any clues, I'd be greatly appreciative.

 

[Peter Jamieson]

Thank you, Peter, much appreciated. I'll jettison Excel and just use
a .csv

or .txt file.

I would test everything as thoroughly as you can before committing to a
particular approach.

Forgive me for asking an elementary question, but I just upgraded to 2007
and after scouring "Help," can still find nothing that tells me how to
extract data from a filled-in form and send it into a .txt/.csv file?

Unless you are willing to do some programming work to use the new
"Content Controls" in Word 2007, you should probably:
a. create the forms using the "Legacy forms controls". You can find
these on the Developer tab (which you can enable in Word Office
Button->Word Options->Popular) in the Controls group. Here, it is the
4th button in the bottom row on the left of this group. Click the
dropdown, and use the top row of controls.
b. (You may be familiar with this part) When you have designed your
form, click Developer tab->Protect Document tab->Restrict editing and
formatting, then check "Allow only this type of editing in the
document", select "filling in forms", then click "Yes, start enforcing
protection" and provide read/write passwords if you want.
c. save your form
d. in Word Office Button->Word Options->Advanced, check "Save form
data as delimited text file".
e. When you fill in the form and click Word Office Button->Save, you
should be prompted to save as a .txt file (you may well get an encoding
dialog box after that.

If you are doing a lot of this, you might prefer to write some VBA to
write the data from your form. I haven't visited it for a while but
there's a lot of forms-related stuff at

http://www.mousetrax.com/

Peter Jamieson

http://tips.pjmsn.me.uk