Mail Merge Issues

[carlsondaniel]

Hello,

I am having a very frustrating problem with my Mail Merge. I am using
both Excel and Word 2007 on Vista and I am trying to Mail Merge a date
from Excel into Word. The problem is it comes in as a number 39546,
rather than April 8, 2008.

Everything I have research has said to just add \@ "MMMM d, yyyy" or
some variation, and update my mail merge code. I have tried this to no
avail. I have tried closing, reopening, updating etc...

I do not want to convert the Excel cell to Text since this date
changes. Any suggestions?

Thank you,

Dan

 

[Peter Jamieson]

This will occur if the OLE DB provider that Word uses to get the data
decides that the column is "numeric" rather than "date", and that will
happen if the first 8 rows in the column containing this cell have
mainly numbers rather than dates in them.

If that is the case and you are able to change what is in those rows,
you may be able to work around the problem.If it isn't, then I do not
know what is wrong but still think it will be because something has
decided that the column is numeric.

Otherwise, your best bet is probably to copy/paste your worksheet into a
blank Word document and use that as the data source (there will,
however, be problems if you have multiline data in Excel and more
columns than the maximum number of columns in a Word table).

Peter Jamieson

http://tips.pjmsn.me.uk

 

[macropod]

Hi Peter,

An alternative approach, if the OP can't get any joy by editing the data source or copying & pasting to a blank Word document isn't
a viable solution, is to convert the dae serial value back to a date via the method outlined under 'Importing Date and Time Values
From Excel and Access' in my Word Date Calculation Tutorial, at:
http://www.wopr.com/index.php?showtopic=249902
or
http://www.gmayor.com/downloads.htm#Third_party

For the OP's purposes, the field code there could be reduced to:
{QUOTE
{SET Delay{=INT({MERGEFIELD ImportValue})}}
{SET jd{=2415019+Delay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
"{dd}-{mm}-{yy}" \@ "MMMM D, YYYY"}
with the addition of 'MERGEFIELD', as indicated, and where 'ImportValue' is the name of the field containing the dates.

 

[Peter Jamieson]

Hi macropod,

Good point.

Since it's unpredictable whether the column would contain things that
look like dates, or things that look like numbers, I guess it would be
possible to use field codes to determine which format the date had (cf.
our recent conversation about ZIPs)

That might be a good option for people left with no alternative,
although it seems to up the reliability and ease of maintenance stakes
if /every/ date, ZIP and perhaps other data type has to be dealt with by
a relatively large set of field codes - which is why it would still be
good if Microsoft could up its game a bit and make Excel data sources -
probably by far the most common one used by casual and small business
users - much more reliable for end users. I sometimes get the impression
that they think that content controls and xml mapping are the "modern"
solution in Office 2007, but that solution is only really accessible to
programmers. Perhaps the next version of Office will have stuff that can
at least get data directly out of XML format files including Excel OOXML.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[cardan]

Hi macropod,

Good point.

Since it's unpredictable whether the column would contain things that
look like dates, or things that look like numbers, I guess it would be
possible to use field codes to determine which format the date had (cf.
our recent conversation about ZIPs)

That might be a good option for people left with no alternative,
although it seems to up the reliability and ease of maintenance stakes
if /every/ date, ZIP and perhaps other data type has to be dealt with by
a relatively large set of field codes - which is why it would still be
good if Microsoft could up its game a bit and make Excel data sources -
probably by far the most common one used by casual and small business
users - much more reliable for end users. I sometimes get the impression
that they think that content controls and xml mapping are the "modern"
solution in Office 2007, but that solution is only really accessible to
programmers. Perhaps the next version of Office will have stuff that can
at least get data directly out of XML format files including Excel OOXML.

Peter Jamieson

http://tips.pjmsn.me.uk

Thank you for the tips. I will see if that code will work. It may just
be something I have to live with if I can't get it to work (my Office
skills are subpar) Thanks again for the input.