Mail Merge - Fields missing within "insert merge field" tab

[jenniclair]

I have an Excel database, with approx 130 data columns, which I use with mail
merge in word for a variety of documents letters, etc to send to customers.
Until last week, this worked with no problem whatsoever, then last week, I
started getting a message that the "DDE connection could not be re-
established", meaning that I couldn't open my word files. I've managed to
find the work around for this on Microsoft's site, by choosing the convertor
method of opening the excel file rather than DDE but now find that a lot of
the merge fields don't work.

When I check in the "insert merge field" tab, the first 59 data fields are
listed, the next four appear together as one field and then there is no
mention of the rest. Can anybody advise please? I am not very good with
computers, so if any explanations could assume I know nothing and take me
through step by step, that would be great!

I have Windows XP Home SP2 2002 version and am using Word & Excel 97.

Many thanks in anticipation!

 

[Peter Jamieson]

If you get automatic updates from Microsoft or an anti-virus software
provider, it is always possible that a recent "security update" has changed
the behaviour of Windows or Office in some way, particularly since there
aren't all that many people (except perhaps Word merge users) who depend on
DDE working properly any more.

However, the problem could also be caused by corruption of some kind or as a
result of a change you have made inadvertently (although I very much doubt
that you have done any such thing).

It's virtually impossible for thoose of us outside the Microsoft camp to
work out if an update is responsible, or which one it might be if it is,
except for the fact that Microsoft tends to roll out security update
packages once a month. However, because DDE connectivity still works OK here
(WinXP Pro+Office 2003) I'd have to say that if there is such an issue, it
does not affect everyone. You should be able to find out what updates have
been applied by opening Control Panel|Add or Remove Programs (check "show
updates"). If you are lucky, you may be able to restore an older version of
Windows, or even be able to use Tools|Accessories|System Tools|System
Restore to go back to a version of your system prior to the most recent
updates. But
a. I'm not sure you have that on XP Home edition
b. System Restore can roll back stuff that you don't want to roll back.

In Excel 2003 there is a setting in Tools|Options|General called "Ignore
other applications" which prevents DDE from working with Excel. I can't
check if the same option is there in Excel 97 but it's probably worth
looking.

It may be worth locating your Word normal template (normal.dot) and renaming
it so that Word creates a new one, just in case it is corrupt.

Other than that, it is probably worth checking to see whether any DDE stuff
works. You can try that by doing the following:

Open Excel

Open Word and start with a blank document

Use ctrl-F9 to insert the special field code braces {}

type the DDE EXCEL SYSTEM SYSITEMS between the {} so you have

{ DDE EXCEL SYSTEM SYSITEMS }

Select the field and press F9 to execute it. If DDE is "working", you should
see no errors (there may be a short delay) and pressing Alt-F9 should reveal
something like:

SysItems Topics Status Formats Selection
Protocols EditEnvItems


If that all works OK, you can go one step further by closing Excel. Then
reselect the field and press F9 again. You should see a message saying that
DDE can't find the server and do you want to start Excel. Say yes. You may
have to reselect and re-execute the field one more time to make it work.

If it doesn't work, something has gone wrong with the DDE system but I can't
tell you what except for the suggestions I've made above.

Peter Jamieson

 

[jenniclair via OfficeKB.com]

Peter,

Thank you for all your suggestions - I've tried them all out and
unfortunately, nothing works!

The DDE is "working" - I carried out the test as suggested and it did all the
things you said it should. In addition, I have another database, which is
working fine, so that doesn't seem to be the problem.

I did wonder whether the excel document was corrupted because I tried to
create a brand new Word mail merge document using the database in question as
a source, with the same result - DDE connection could not be established, so
I used the convertor method of opening it, which did work but didn't give me
all of the potential merge fields that it should have done, as I described in
my original message. I therefore tried copying and pasting all of the data
from the (possibly) corrupt document into a brand new excel workbook with a
new name and tried to merge to that but with the same result.

I have tried saving the database to a CD and taking it to a different PC,
where I started again, with a blank Word doc and tried to merge to the Excel
file on the CD but again with the same results, so I'm assuming it's not the
computer that's at fault.

What I have discovered, while playing around, is that if I just copy a small
portion of the database out into a new file - keeping the same number of
columns but only taking a few of the rows - it will not only merge to a new
Word doc using DDE but it will give me all of the column headers in the drop
down "insert merge fields" list. This would make me think that there was a
maximum file size that can be merged, if it wasn't for the fact that it had
worked previously and I have not increased the size of the database since the
last time I used the mail merge successfully. To test this theory, however,
I have tried selecting a range when the convertor asks if I want to use the
whole workbook or just a part of it, when I first open the Word document but
it still does not give me the correct fields in the drop down "insert merge
field" menu.

I am at a total loss as to what to do - obviously, it's causing me a major
headache that I can't use any of my merge documents (needless to say, I seem
to need them all the time at the moment, while it's not working!). If there
is anyone out there who has come across this before and can tell me how to
sort it out, I would be extremely grateful. Once again, if you can give me
step by step instructions for any possible solutions I would appreciate it as
I really do not know anything more than the basics when it comes to computers!


TIA.

 

[Cindy M.]

Hi Jenniclair,

I did wonder whether the excel document was corrupted

Yes, that's the next thing I'd test. Assuming the worksheet
has less than 56 columns, you should be able to copy/paste
it into a Word document, as a Word table. Can you do that,
and do all the columns (and rows) come across? If yes, and
you link to this as the data source, do you get everything?

If an Excel sheet is corrupt, you could end up carrying the
problem across by copying/pasting the cells. The better
"test" is to export/save to a delimited text file format.
Then import the result into a new Excel 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

 

[Peter Jamieson]

My best guess is that your Excel document /has/ been corrupted and that the
corruption is not obvious - i.e. when you look at the worksheet, it contains
what you expect.

I think the only way to test that is to find a way to reconstruct the
spreadsheet without re-introducing the problem (which, for example, using
copy/paste will probably do). However, how to do that depends partly on how
complex your spreadsheet is. If it is very simple (e.g. all you have done is
enter the data, never applied any formats to cells, and have done simple
stuff like adjust column widths, then the simplest method might be to save
to .csv or one of the other "non-Excel" formats listed in the "Save as type"
dropdown in the Save As dialog, then open the saved file and save it in
Excel format. However, you may need to avoid simple formats such as .csv if
your data has characters such as double-quotes or commas in it (and
double-quotes may cause problems anyway, although I think you would have
noticed when you were doing your experiments with reduced record counts).

If your sheet is more complex, it might be pssoble to do something with
Excel VBA. But let's move one step at a time...

Peter Jamieson

 

[jenniclair via OfficeKB.com]

Cindy,

Thanks for your message - unfortunately the spreadsheet has around 130
columns, so didn't try saving as a word table.



Peter,

Superstar! I saved it as .csv and then saved that file as an Excel doc and
hey presto, it all seems to work!!

Thank you so much for your help - I've never even come across the .csv thing
before, so would never have worked it out for myself. I really appreciate
all your assistance - thanks again.

Jennifer.

 

[jenniclair via OfficeKB.com]

**** UPDATE ****

In case anyone's interested....

After following Peter's advice re saving as .csv and then saving that as an
excel doc, I got the database working. I then sat and put my formatting back
in - nothing drastic, just column widths, some colours on some cells and a
couple of basic formulae. I then found that the problem had occurred again,
namely that when I tried to open the word doc to mail merge, I got the error
message that the DDE connection could not be re-established, so had to open
using the converter method, which meant that the majority of potential merge
fields were missing from the drop down menu.

I went through the whole process again - save as .csv, save that as excel -
and again, got a working database. Put some formatting in and it stopped
working. By a process of elimination, I have discovered that it is the
"wrap" function that seems to cause the problem. I always wrap text in cells,
so that I don't have to have the columns so wide (shrinking to fit makes the
cell contents too small to read) but as soon as I wrap, the DDE stops working.
I have no idea why this is, as it has always worked in the past but it very
definitely does seem to be this. As soon as I take the wrap off, I can merge
to the database without any problems!

This means that I now have to either have very wide columns or shrink to fit,
neither of which is ideal, or I have to remember to take the wrap off before
I try to merge to the database. Not a major problem but an irritation,
nonetheless!!

If anyone else encounters a similar problem, it may be worth just taking off
the wrap and seeing if this resolves the issue for you too!

My thanks again for the assistance.

Jennifer.

 

[Peter Jamieson]

Jennifer-

Thanks for that bit of feedback - I'll have a look at some point. I don't
actually use Excel a lot but have encountered another oddity with text
wrapping a few years ago.

Partly because Excel's DDE support has not been maintained in new versions
of Excel, it is likely that a number of Excel features will cause problems
with DDE. A simple example is that DDE can only see one sheet in a workbook.
However, as you say it cannot be wrapping on its own, because it used to
work (unless there has been a recent change in this area).

Peter Jamieson