This has worked fine so far, but now I need to increase the number of
columns in my master document to more than 256. It follows from here that
I'll have to split the master worksheet into two separate sheets. In that
case, will I be able to merge them into Word?
No.
Is it possible to have Word merge data from a database that is
arranged in rows instead of columns?
No.
If not, is there any other
solution?
There may be, but it depends on how simple your data is and you may need to
experiment. As further background
a. some column limits are imposed by the product (Excel 2003's column
limit, for example). Others are imposed by the mechanism Word uses to get
data from its data source (either DDE, a text file converter, ODBC or OLE
DB). Although I don't think OLE DB itself imposes any limit on column count
(I don't know for sure), the Jet OLE DB provider used to get data from Excel
does impose a limit - and in fact all the "Desktop Database providers" from
Microsoft also impose a maximum of this kind, as far as I know.
b. You can move to Excel 2007 and relax the column limit as Doug mentions.
But unfortunately there is still a limitation in the providers (now called
ACE rather than Jet) so when using Excel as a data source you are stuck with
a maximum of 255 columns. With DDE, the maxium is somewhere below 100 I
think).
c. It is still possible to use a plain delimited text file as a data source
and exceed this limit, but you will probably find that you cannot use fields
with multiline text reliably, and there are other quirks when using text as
a data source (e.g. Word may decide to use the OLE DB provider to read it,
in which case you're back to the 255 maximum. Or Word may recognise the
encoding of the file incorrectly and you'll end up with chines characters in
your document when there should be latin characters- that kind of stuff. So
you need to verify that your data works correctly after it's exported to a
delimited text format. There can also be problems if your data contains the
character you intend to use as a field delimiter (usually <tab> is best), or
contains the text delimiter ("), or if some of the data lines are
particularly long.
But of course with Excel 2003 even exporting to text format will be
difficult because you have to export from two sheets. It's probably worth
asking in an Excel group about that because there may be some simple way to
do it. However, if I had to do it I would probably write some Excel VBA that
looped through every cell in the spreadsheet and output it to a text file,
inserting delimiter characters after each cell's data and each row. You can
either output to a plain text .txt type file, or use Word Automation to put
the data into a Word document (which may have some advantages - I don't
know).
A possible advantage of using Excel 2007 is that you may be able to export
more than 255 columns, or copy/paste the data into Word (use Paste Special
and choose the unformatted text option)
Other possible approaches:
- find a data source with an OLE DB provider or ODBC driver that allows
more columns, and store your data there. I don't have any suggestions on
that one
- keep your data in Excel 2003 but "roll your own" merge application,
using Excel automation to get the data from whichever sheet it happens to be
in, and stuffing it into your Word document where you need it.
