Using DDE as source in mail merge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Glibby Gibson - 23 May 2006 03:46 GMT

Word 2003 and Excel 2003 - I have some fields in an XL file that are
percentages - show as 14.5%, 20.8% etc. in XL. No formulae in the cell -
just hard values. When I mail merge to a Word doc some of the percentages
merge correctly - 20.8%, and others don't - 0.1449657838267 etc.

I've used mergefield switches to make the merge work correctly (but it's
pretty slow to add for all the fields I'm merging) and I've seen a post about
switching back to a DDE interface - which is how I'd like to go.

Here's the rub - when I select the XL file, select DDE and select the entire
spreadsheet, I get a message that Word cannot open the source file. If I try
and open a different XL file under DDE it works fine and I get the data merge
I want - although it's on the wrong XL file! Is it possible that all my
original trial and error work has corrupted the original XL file and I need
to rebuild (hope not, it's a LONG process) or is there something I'm missing
here?
Thanks - sorry for long post.
Glibby

Reply to this Message

Graham Mayor - 23 May 2006 06:06 GMT

DDE can be a bit flaky which is presumably why Microsoft changed the default
connection method.

If your worksheet is formatted using numbers and the cells formatted as
percentages, then they should be imported into Word as long decimal numbers,
which, as you realise, can be formatted with switches thus { ={ MERGEFIELD
Number } * 100 \# "0%" }. I am unable to force any percentage to come across
intact useing the default connection.

If you enter the fields manually from the keyboard, it takes but a moment
longer to add the switches - and you only have to do it once. The document
can be saved for re-use. You could even assign an unformatted autocorrect
entry of (say) =% to * 100 \# "0%" for speed of entry.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Word 2003 and Excel 2003 - I have some fields in an XL file that are
> percentages - show as 14.5%, 20.8% etc. in XL. No formulae in the
[quoted text clipped - 16 lines]
> Thanks - sorry for long post.
> Glibby

Reply to this Message

Glibby Gibson - 23 May 2006 15:09 GMT

Thanks for the speedy response. Agreed - definitely flaky.

However, of the 120 or so % fields in my XL file, approx 20% merge across
correctly - the rest don't. So an autocorrect will cause the 20% to multiply
by 100 and blow up - and as you can see my issue is not about saving a doc to
reuse and merge a couple of cells. I have 251 columns (255 is the XL max.)
across 238 rows with a variety of formats - text, %, $, xx.xxx, etc.

A little more late night trial and error proved that the guilty file could
be made to work by deleting certain rows. I'm still working on identifying
what in those rows caused merge to belch! I suspect it's either a hyperlink,
or a single cell has a weird format set.

Thanks again

> DDE can be a bit flaky which is presumably why Microsoft changed the default
> connection method.
[quoted text clipped - 30 lines]
> > Thanks - sorry for long post.
> > Glibby

Reply to this Message

Cindy M -WordMVP- - 23 May 2006 17:32 GMT

Hi =?Utf-8?B?R2xpYmJ5IEdpYnNvbg==?=,

> Thanks for the speedy response. Agreed - definitely flaky.
>
[quoted text clipped - 8 lines]
> what in those rows caused merge to belch! I suspect it's either a hyperlink,
> or a single cell has a weird format set.

Just FYI, and Excel file can become damaged, just as a Word file can. It's
possible that the rows you deleted were damaged. If you still have a copy of the
entire workbook, try exporting the table to a delimited, plain text file, then
import it into a new workbook.

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 :-)

Reply to this Message

Glibby Gibson - 24 May 2006 01:33 GMT

Thanks all.

The Excel file is fine - the issue is the record length - too many chars in
some rows. I don't know what the mailmerge limit us under DDE but it's
obviously less than the other connection methods. So I chopped out some
columns and it's working great.

FYI - if the rogue row is a good way down the spreadsheet the conversion
fails from that point onwards w/o an error message and you won't realize
until much later. The error message only kicks in if the first row is too
large.

BTW - is there any easy way to determine the # of chars in a record (row) -
ie the max size DDE will support?

Thanks again

> Hi =?Utf-8?B?R2xpYmJ5IEdpYnNvbg==?=,
>
[quoted text clipped - 23 lines]
> This reply is posted in the Newsgroup; please post any follow question or reply
> in the newsgroup and not by e-mail :-)

Reply to this Message

Cindy M -WordMVP- - 28 Jun 2006 11:44 GMT

Hi =?Utf-8?B?R2xpYmJ5IEdpYnNvbg==?=,

Usually, we find out the limitation is either 256 or 512 (I forget which, but
those are the "usual suspects" when Word hits a character limitation).

> The Excel file is fine - the issue is the record length - too many chars in
> some rows. I don't know what the mailmerge limit us under DDE but it's
[quoted text clipped - 8 lines]
> BTW - is there any easy way to determine the # of chars in a record (row) -
> ie the max size DDE will support?

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 :-)

Reply to this Message

Rate this thread: