[Rebecca Dell]

Hi everyone

I'm merging from excel to word with OLE. I was previously using DDE with no
problems but last week the DDE links (to all my mail merge files) decided to
give up. I've sorted out most of the formatting issues now with switches etc
but I have one left:

I have a text field in excel which only seems to import into word properly
if it is populated by a number. If the field has letters/symbols rather than
numbers, Word displays a zero. I want Word to show whatever is in the field
as it is (sometimes numbers, sometimes text) but I can't find a switch that
would do this for me.

Any ideas?

Thanks in advance
Rebecca

[Cindy M.]

Hi ?B?UmViZWNjYSBEZWxs?=,

I'm merging from excel to word with OLE. I was previously using DDE with no
problems but last week the DDE links (to all my mail merge files) decided to
give up. I've sorted out most of the formatting issues now with switches etc
but I have one left:

I have a text field in excel which only seems to import into word properly
if it is populated by a number. If the field has letters/symbols rather than
numbers, Word displays a zero. I want Word to show whatever is in the field
as it is (sometimes numbers, sometimes text) but I can't find a switch that
would do this for me.

Select the column in Excel. From the Data menu: TextToColumns. The last step in
this little wizard is the important one: set the column to explicitly be Text
(not General, the default).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

[Rebecca Dell]

Hi Cindy
Thanks for that - it did the trick. I had already changed the Format Cells
format to Text but it didn't make any difference until I followed your
instructions.
Thanks again
Rebecca

"Cindy M." wrote:

Hi ?B?UmViZWNjYSBEZWxs?=,

I'm merging from excel to word with OLE. I was previously using DDE with no
problems but last week the DDE links (to all my mail merge files) decided to
give up. I've sorted out most of the formatting issues now with switches etc
but I have one left:

I have a text field in excel which only seems to import into word properly
if it is populated by a number. If the field has letters/symbols rather than
numbers, Word displays a zero. I want Word to show whatever is in the field
as it is (sometimes numbers, sometimes text) but I can't find a switch that
would do this for me.

Select the column in Excel. From the Data menu: TextToColumns. The last step in
this little wizard is the important one: set the column to explicitly be Text
(not General, the default).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

[Peter Jamieson]

FWIW what is going on here is that you have to set one of the values in the
first 25 data rows of the relevant column so that the OLEDB provider sees it
as text. But if you just select the column and use Format|Cell to apply the
Text format, it doesn't do quite the same thing as Cindy's method (you may
be able to tell because Excel marks all the numeric cells with a warning
when you use her method. In fact, it should be enough to retype /one/ of the
numeric cells in the first 25 rows with a single quote in front to tel Excel
it's "text", e.g. '5 instead of 5.

Since only doing one of them may introduce inconsistencies and/or problems
if you sort the column, doing the lot seems more sensible and Cindy's method
(new to me!) does the trick rather nicely.

Peter Jamieson
"Rebecca Dell" wrote in message
...
Hi Cindy
Thanks for that - it did the trick. I had already changed the Format
Cells
format to Text but it didn't make any difference until I followed your
instructions.
Thanks again
Rebecca

"Cindy M." wrote:

Hi ?B?UmViZWNjYSBEZWxs?=,

I'm merging from excel to word with OLE. I was previously using DDE
with no
problems but last week the DDE links (to all my mail merge files)
decided to
give up. I've sorted out most of the formatting issues now with
switches etc
but I have one left:

I have a text field in excel which only seems to import into word
properly
if it is populated by a number. If the field has letters/symbols
rather than
numbers, Word displays a zero. I want Word to show whatever is in the
field
as it is (sometimes numbers, sometimes text) but I can't find a switch
that
would do this for me.

Select the column in Excel. From the Data menu: TextToColumns. The last
step in
this little wizard is the important one: set the column to explicitly be
Text
(not General, the default).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply
in the newsgroup and not by e-mail :-)

[Cindy M.]

Hi Peter,

FWIW what is going on here is that you have to set one of the values in the
first 25 data rows of the relevant column so that the OLEDB provider sees it
as text. But if you just select the column and use Format|Cell to apply the
Text format, it doesn't do quite the same thing as Cindy's method (you may
be able to tell because Excel marks all the numeric cells with a warning
when you use her method. In fact, it should be enough to retype /one/ of the
numeric cells in the first 25 rows with a single quote in front to tel Excel
it's "text", e.g. '5 instead of 5.

In my experience, this doesn't work with the OLE DB provider. As far as I've
been able to tell, it will still see the column as "general" and then not pick
up purely numeric entries (passes an "empty string"). Or has something changed
since the last time I tested g?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

[Peter Jamieson]

Hi Cindy,

As you know we are in "apparently unpredictable" territory here :-)

I have been experimenting recently but haven't collated all the info yet.
I'll post somewhere when I have. However, applying text format /alone/ is
definitely not enough It is perhaps significant that when you convert to
text using the method you specified, Excel does actually convert values such
as "5" to "'5", and what I suspect is happening is that as long as you have
at least one cel in the first 25 that Excel presents in its formula bar
starting with a single quote, OLEDB will see the column as being mixed/text.

If you have counterexamples, please let me know. Of late, I haven't ben
focussing on sheets with fewer than 25 rows, for example :-)

Peter Jamieson





Personally I think it would be ever so much easier if only
a. the OLEDB provider was able to present Word with /whatever the user had
put in their Excel cells/. Unfortunately, daftness in "the industry" are so
obsessed with strong typing that they probably regard people who mix text
and numbers as some kind of ignorant deviant
b.

The OLEDB provider definitely looks at the first 25 data rows (i.e. not the
header row, as far as I know) to try to determine the column format. If it
thinks the data is all numeric, it will process non-numeric values
incorrectly (fortunately, or perhaps not, it does not actually reject them).
Even the 25-row factor is a bit of a moveable feast.

The trouble is that if you don't take account of that 25-row factor, the
results of other experiments may have limited value.

As far as I can see, wh


So even there, there are at least 3 experimental possibilities as far as
mixed text/numeric colmns are concerned:
a. there are 25 data (i.e. non-header) rows or fewer.
b. there are more than 25 rows fewer than 25 rows an 25 rows



The question then boils down to "what is it about an Excel cell that makes
the provider think it is numeric?" (and of course other things that are
defined outside a single cell may come into play here).

It definitely isn't /just/ the Format/Cells format. If you select the column
and change all the cell formats to text, Excel obligingly shifts all the
numbers to the left, accepts that they are numbers (i.e. doesn't display any
"warning triangles"), but the OLEDB provider still decides that the column
is numeric.

However, nor is it some weirdo thing like "if the cell is text, but there's
a number, and Excel has flagged a warning/error, and the user hasn't told
Excel to ignore the error"



My own recent experiments suggest that the OLEDB provider does not really
care what the cell format is: if you change all the cells in the column to
"Text" format, Excel obligingly shifts all the numbers over to the left and
does not display one of its warning traiangles in the upper left corner of
the cells, suggesting that it now sees them as being text values. But the
provider still treats them as numbers, and if it thinks the first 25 data
rows (i.e. forgetting about the heading row) are numbers, it treats the
remainder of the column as numeric.

However, if you had (say) the number '5' in a cell and you change it to '5,
Excel displays the 5, displays its warning triangle,
Fortunately it doesn't go the whole hog and complain when there's text in
the cell.

seems to think they are numbers.

In contrast, if I change one of the numbers to be text using e.g. '5 instead
of 5, Excel flags a warning

"Cindy M." wrote in message
news:VA.00000208.012e548b@speedy...
Hi Peter,

FWIW what is going on here is that you have to set one of the values in
the
first 25 data rows of the relevant column so that the OLEDB provider sees
it
as text. But if you just select the column and use Format|Cell to apply
the
Text format, it doesn't do quite the same thing as Cindy's method (you
may
be able to tell because Excel marks all the numeric cells with a warning
when you use her method. In fact, it should be enough to retype /one/ of
the
numeric cells in the first 25 rows with a single quote in front to tel
Excel
it's "text", e.g. '5 instead of 5.

In my experience, this doesn't work with the OLE DB provider. As far as
I've
been able to tell, it will still see the column as "general" and then not
pick
up purely numeric entries (passes an "empty string"). Or has something
changed
since the last time I tested g?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply
in the newsgroup and not by e-mail :-)