Word 2007/Excell 2007 - random loss of field contents on mailmerge

[John]

I am using Windows XP SP3 and MS Small Business Exchange Server 2003.

My office recently upgraded to Office 2007 from Office 2000. My issue
regards mailmerge in Word 2007 using Excel 2007 as datasource/OLEDB. I would
prefer to use DDE, but that option did not work well for us.

We currently use many Word docs that have the following merge field code and
formatting:

{MERGEFIELD InjuryDate\@"M/d/yyyy"}{MERGEFIELD Injury_Date_Text}

The corresponding fields in Excel database contain date only, text and date
combined or can be completely blank. If one or the other field is empty and
one has data, I want the field with data to insert that data in the Word doc.
If both fields have data I want both fields to insert that data in Word doc
next to each other. If both fields are blank then Word doc should be blank.
I do not want “zero†to insert for any blank fields.

Examples of data in those separate fields are as follows:

Injury Date field:
6-12-09
8/15/08

Injury_Date_Text:

Series
And/or 8/16/03
Every day thereafter until 3/2/2007
Series beginning 10/01 – each and every working day thereafter

My problem:
Our merging process was working fine with contents of the above fields
inserting properly into all of our Word docs. However, after a couple of
days various issues began to arise when merging. If the Injury Date field is
blank a zero would print. Sometimes the Injury Date Text field would not
insert its contents into the Word doc. Sometimes, the fields would insert
the contents properly, but it was all random. After reading several posts on
this Discussion Group reformatted all columns containing Date and/or Text
contents via the Data/Text to Columns suggestion. As an aside, we have
several numerical and date columns in the datasource worksheet. All were
randomly working or not working. I reformatted my word mergefields using Mr.
Mayor’s link to formatting merge fields in Word information. Still we are
experiencing problems. I realize my formatting above does not deal with the
“zero†issue. I would like help with that. But, what I do not understand is
why Word randomly chooses to input contents of fields or not.

Please help. Many people access these documents and I need to be secure in
the fact that al the information contained in our database for each
individual records are inserting into our word docs when we merge.

Thank you in advance for any help you can provide.

 

[Peter Jamieson]

But, what I do not understand is

why Word randomly chooses to input contents of fields or not.

There is an "explanation" of most of this behaviour at
http://tips.pjmsn.me.uk/t0003.htm. It doesn't go into the question of
"why" in the sense of why Microsoft has never fixed this stuff because I
don't know and can only speculate.

I guess you may already have read that as you have mentioned one or two
techniques described in there.

The problem is "what to do about it". If DDE doesn't work for you, the
options are limited. Have you found that it simply no longer works
reliably in Office 2007, or are you trying to use Workbook techniques
that will not work with it, e.g. multi-sheet workbooks? If you are using
2007 .xlsx format files, it's possible that reverting to .xls will help.

Failing that...
a. It may be worth trying some of the other techniques described in
the web page I have referenced, but they mostly involve having more
control over the content of the workbook than most people are likely to
have (e.g., ensuring that the top eight rows have dummy data in them to
ensure that Word "sees" the data in a more useful way.
b. Personally, for precisely these reasons, I would always prefer to
keep data in a data source that I could rely on (e.g. one of the free
versions of SQL Server) but I know that (1) that would be a big change,
(2) you would still probably want to use Excel and (3) Excel really only
does read-only communication with databases
c. Some other possibilities, all of which would involve some
programming in either VBA or perhaps a .NET language. I don't currently
have code for any of them though:
- programmatically copy/paste the data into a Word document, and
use that as the data source for the merge. That is more likely to work
well if the number of columns is smaller than the maximum number of
columns in Word, because then the data should paste as a table and
potential problems with double-quotes in the data should go away. I
don't have code for that right now.
- automate Excel to put the data somewhere else, row by row. For
example it would not be too hard to put it in an Access/Jet/ACE database
.. Then use that as the data source.
- instead of using Mailmerge to put data in the document, automate
the document to get data from Excel and insert it. NB, the "modern",
Office 2007 way to do that involves Content Controls and a thing called
the XML data store.

FWIW, I do not really understand why Microsoft has chosen a design which
takes a common wordprocessing function out of the hands of the users and
forces them to rely on programmers - it's almost a reversal of why they
were successful inthe marketplace in the first place. "Commerce" may be
the reason but it's a very poor one from the users' perspective.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[John]

Thank you for your help Peter. You rcomments are very useful.

I think, for now, I will try to put a few dummy rows in the database while I
research and test what other methods I might want to change to for the future.

I wonder if you could point me in the direction of "How to" sources
regarding your last paragraph <instead of using Mailmerge to put data in
the document, automate
the document to get data from Excel and insert it. NB, the "modern",
Office 2007 way to do that involves Content Controls and a thing called
the XML data store.>

I am unfamilar with the XML data store. I played briefly with Content
Controls in Word 2000, but not yet had time in 2007.

Any sources on this subject would be helpful. Knowledge base is practically
useless and so far the books I have come across are basic info.

I will read the link you posted and see what further info I can pick up from
it.

Again, thank you for spending so much time with my problem and offering so
many choices to consider.

John

 

[Peter Jamieson]

Content Controls are new in Word 2007, so I guess you may be thinking of
an earlier technology in Word 2000 - perhaps forms controls etc.

For content controls etc. there are various places you could start, e.g.
for "doing what mailmerge does" you could try

http://blogs.msdn.com/erikaehrli/archive/2006/08/11/word2007DataDocumentGenerationPart1.aspx

or you could go to http://msdn.microsoft.com and search for either

word content controls
or
word content controls mailmerge

and see what turns up.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[John]

Thank you Peter. Once again you have been very helpful.