My source is Excel, in which I have 4 columns set up. In 1 of my many Rows,
all 4 columns say 'n/a', instead of a number in each. When I merge these
columns into the fields in Word, I get '0.00 .00 n/a n/a' (4 fields). When
the fields in Excel have a number, instead of 'n/a', everything works just
perfectly. All columns in Excel are formatted exactly the same. All fields in
Word are formatted the same with the only difference being the column header
it is addressing. And keep in mind that when there are numbers in Excel, it
all works perfectly. Any ideas for this big puzzle??
--
DM

Assuming you are using Word 2003/2002, the easiest way to get Word to fetch
the Excel data "as seen" is usually to change the connection method from the
default (OLEDB) to the old method (DDE). To do that, check Word
Tools|Options|General|Confirm convesions at open, go through the connection
process again, and select the DDE option when it is offered.

However,
a. it doesn't work in all cases
b. it starts a copy of Excel (i.e. you must have Excel on your system -
OLEDB can just open the workbook without Excel), which complicates things,
particularly if you need to distribute your solution.

Another possibility is to export the data in some way, e.g. if you have 63
columns or less, you may find that you can copy/paste into a new Word
document that you can use as the data source.

Partly for the benefit of anyone else who may be reading this article...
This inconsistency is /probably/ caused by the way OLEDB (and ODBC) decides
what type of data is in each column in an Excel sheet. Roughly speaking (I
do not know the whole story here):
a. Both OLEDB and ODBC use the (Access) Jet engine to get data from Excel
b. In the Windows registry there are some entries that affect the way Jet
processes Excel data, under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel

c. The TypeGuessRows values specifies how many Excel rows Jet will look at
to try to determine the type of data. The default is 25. If you set it to 0,
Jet should scan all the rows and Word's behaviour will change. But you
cannot do this on a file-by-file basis.
d. The ImportMixedTypes value - this can either be "Text" or "Majority
Type".
If it is "Majority Type", Jet will, for example, decide that if 13 cells
out of the 25 it looks at in a column are numeric, then the column will be
numeric and non-numeric values will be converted ("cast") to numeric values
if possible, and otherwise returned as nulls. If there are an equal number
of numeric and non-numeric cells, the column will be numeric.
If the ImportMixedTypes value is set to "Text", when Jet finds mixed
types, it chooses the text type and converts everything to that. NB, in
particular, this will truncate long memo text to 255 characters.
A possible exception is that where the column contains 2 or 3 date/time
types, Jet may treat the column as date/time.

e. When a program such as Word connects via OLEDB, it can pass information
in a connection string that can override some registry settings for the
current connection. For Excel, it is possible to specify "Extended
Properties", e.g.

Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'

The value of HDR is to do with whether or not there is a header row. "IMEX"
stands for "IMPORT/EXPORT" - it can be 0 (IMPORT), 1 (EXPORT) or 2 (LINKED,
i.e. the sheet is updatable) . If the value is 0 or 2, the "Majority Type"
approach is used. If the value is 1, I believe that Jet honours the value of
ImportMixedTypes in the registry. So you can probably get Word to change the
way it handles mixed columns in Excel as long as you use VBA to issue an
OpenDataSource call with the correct connection string (or maybe you would
have to go via a .odc or .udl file)

Anyway, in your situation, what I would guess is that your first and second
columns probably have numeric data in the first 25 rows of the spreadsheet
and your third and fourth columns probably have another n/a value in the
first 25 rows. I would be interested to know if that is the case.

If that isn't correct, I have no other explanation.

Peter Jamieson

"Duane M" wrote in message
...
My source is Excel, in which I have 4 columns set up. In 1 of my many
Rows,
all 4 columns say 'n/a', instead of a number in each. When I merge these
columns into the fields in Word, I get '0.00 .00 n/a n/a' (4 fields).
When
the fields in Excel have a number, instead of 'n/a', everything works just
perfectly. All columns in Excel are formatted exactly the same. All fields
in
Word are formatted the same with the only difference being the column
header
it is addressing. And keep in mind that when there are numbers in Excel,
it
all works perfectly. Any ideas for this big puzzle??
--
DM