Using DDE as source in mail merge

[Glibby Gibson]

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

 

[Graham Mayor]

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.


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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Glibby Gibson]

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

 

[Cindy M -WordMVP-]

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

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.

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

 

[Glibby Gibson]

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

 

[Cindy M -WordMVP-]

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
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?

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