Word Mailmerge with more than 256 fields

[Keith]

Morning everyone,

I'm used to doing simple Word mailmerges from Excel data
using automation in VBA but have a need to use a
datasource other than Excel because of the implicit 256
column/field limit.

I have some XML data which I need to use for a mailmerge
which has in some cases just over 400 fields.

I've created a 2 line CSV file with 400 fields/values (a
row of headers & a row of data) & I'd like to use that as
the datasource.

Does anyone have any experience or code examples of how to
do this with either CSV or textfiles.

I can't seem to get the merge to work with a CSV as it
complains that the datasource is not valid. I've seen some
exampls of a SQL query syntax for the merge datasource but
can't get this to work.

Another avenue I tried was to try to use the OWC (Office
Web Component) spreadsheet as a datasource as this has
columns to ZZ in 2000 or ZZZ in XP/2003. Couldn't get that
to work either

Any help/tips the list can give would be much appreciated

Regards

Keith

 

[Peter Jamieson]

Morning everyone,

I'm used to doing simple Word mailmerges from Excel data
using automation in VBA but have a need to use a
datasource other than Excel because of the implicit 256
column/field limit.

I have some XML data which I need to use for a mailmerge
which has in some cases just over 400 fields.

I've created a 2 line CSV file with 400 fields/values (a
row of headers & a row of data) & I'd like to use that as
the datasource.

Does anyone have any experience or code examples of how to
do this with either CSV or textfiles.

I can't seem to get the merge to work with a CSV as it
complains that the datasource is not valid. I've seen some
exampls of a SQL query syntax for the merge datasource but
can't get this to work.

The versio of Word is relevant here as
a. Word 2002/2003 tends to try to use an OLEDB provider as the default
way to get data, and you will probably run into problems with most of
those when you have large numbers of fields
b. Word 2000 will typically try to use either an internal text
converter or ODBC, and you will run into problems with ODBC on column
count, and the text converter if you have non-ASCII characters

If you are using Word 2002/2003, try:
a. giving your csv file a .txt extension
b. checking Word Tools|Options|General|COnfirm conversions on open
c. go through the connection process and select the Text files (*.txt)
open. If you have to name the file .csv, you will need to check the
"Show all" box to see the Text files option.

However, I have used quite simple test files to check this - more
complexity (such as quotes round fields, commas in the data etc. may
create problems).

In VBA, you /may/ be able to achieve the same thing by setting Name to
the file path name in OpenDataSource, setting Connection, SQLStatement
to "", and using
Subtype:=wdMergeSubtypeOther
but you will need to test that too.

You may have more difficulty if you have non-ASCII data and have to use
a Unicode-encoded text file - in that case you wil lprobably see the
encoding dialog box, and I don't think you wil be able to avoid that in
code. Probably your best bet in that case would be to open the file
programmatically, specifying the encoding (which you can do in a
Document Open, but not in OpenDataSource) and save it as a
comma-delimited Word (.doc) file, then use that as the data source.

Peter Jamieson

 

[Guest]

Thanks for that Peter,

I did make some progress, in that I can now use either CSV
or TXT file extensions.

A problem I had with a dialog box asking for field &
record delimiters was because of using the wrong character
for the Newline / vbCr

Also the problem about the datasource not being valid was
due to one extra value compared to field header (basically
a comma to many in line 2)

Now have working datasource with 684 fields

Thanks

Keith