Word Mailmerge from Excel - Dates show as numbers

[KenWright]

Document at work in Word that brings in a bunch of data from an excel spreadsheet (Approx 300 rows of data). Most of imports just fine, but some of the dates are coming across as their numerical equivalents, eg 13/05/2004 will perhaps appear as 38120. Odd text fields come across 0s as well. have trawled through thge keyword search hits on mailmerge, but know almost zip about mailmerge and can't see anything that would be applicable, unless you have to specifically format fields within the mailmerge. that having been said, it doesn't explain the text entries coming across as 0s.

Any pointers to save some of this hair from getting pulled

Regards
Ken....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

No matter - in slower time, have managed to find a good reference as to what is happening:-

http://homepage.swissonline.ch/cindymeister/MergFram.htm

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

Ken,

Would a Date format work?
[tt]
{MERGEFIELD F1 \@ dd/mm/yyyy" }
[/tt]
?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

Hi Skip - I'm assuming so. I've never touched Word really, so was out of my depth when asked for why a spreadsheet was messing up the mailmerge. Instinctive reaction was, "Don't you dare blame my beloved Excel app", but wasn't sure

Problem apparently was that it used to work but no longer did. Turns out that it used to work last year (Tax forms being issued), but doesn't this year, and the reason being (I think) is that we have upgraded to Office XP. Word XP no longer uses DDE to do the mailmerge but uses OLEDB instead. The DDE linkage meant that all the formatting came across when the two environments (Word / Excel) talked to each other via DDE, but OLEDB means only raw data is brought across.

Solution being (hopefully), and as you have rightly said, to use a formatting code in the mailmerge field within Word itself. I'll find out tomorrow I guess when I give them this as a possible solution

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Dreamboat]

That's exactly why it doesn't work anymore. And don't you dare blame my beloved WORD!!

Hee hee...

Anne Troy

http://www.amazon.com/exec/obidos/tg/detail/-/0972425845/104-9778384-2391158?v=glance&s=books

 

[SkipVought]

Uh oh!

It's the BLAME GAME!

the xls-ers vs the doc-ers

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Dreamboat]

Humph. We know which side YOU'RE on, traitor!

Anne Troy

http://www.amazon.com/exec/obidos/tg/detail/-/0972425845/104-9778384-2391158?v=glance&s=books

 

[KenWright]

rotflmao

Hey Anne, I do still have a quickie that you may be able to help with. The mailmerge doc brings in data from some 50 odd different columns of data, and where the data in the spreadsheet is blank, nothing is supoosed to get pulled across into the word doc. But, in some of the fields, a 0 is getting pulled across, even though the spreadsheet is blank for that field.

Any thoughts on that one??

Cheers
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[macropod]

Hi Ken,

Excel and Access both store dates as numbers - it's only the formatting that converts the numbers to something that looks like a date. The same applies to times also. Dates are integers, times are decimal values.

The numbers being pulled in via your mailmerge are serial date numbers. You can convert those numbers back to dates via a bit of field manipulation. See the Word document at:

http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=249902

for the details.


As for the empty cells coming across as 0s, if you're dealing with numeric data, this can be controlled via a numeric picture switch. Select the errant field and press Shift-F9. It should look something like:
{MAILMERGE Data}
Then add the picture switch '\# 0;0;' so that the field looks like:
{MAILMERGE Data \# 0;0;}
Then press F9 again to update the field. Depending on the type of data you're dealing with, you might need to add currency symbols, decimals and thousands separators, etc. If you're dealing with text data and getting 0s, that too can be controlled, via an IF test. See the Word document at:

http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=365442

for more details on both scenarios.

Cheers