Mail Merging with Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

keith2816 - 22 Dec 2003 18:19 GMT

I'm using word97 for mail merge and my data source is in excel 97
format.

I have a problem when i try to merge data source with many records.
what happen then was that i used the mail merge query function to
filter the column and exclude those cell with blanks on the selected
column.

However, there's some errors on some of the merged result.
Some of the pages will show incorrect merge field for example
some of mergefield will repeat the value designated for the previous
record and copied the same values down across the next few records.

I have tried many ways but failed. THe only tedious solution is to
insert another blank row on those affected excel data source and then
perfom mail merge.This means that my excel data source will have a
blank row in between records for merging to be successful.

Anyone can help?

------------------------------------------------

Reply to this Message

Cindy M -WordMVP- - 23 Dec 2003 11:06 GMT

Hi Keith2816,

This sounds like a problem that occasionally crops up when using DDE to
merge to Excel. Often, the problem is a damaged Word file, but it could
also be a damaged Excel file...

If you rename Normal.dot, start up Word and create a new merge document
(just a test) to the Excel file is the merge result correct?

> I'm using word97 for mail merge and my data source is in excel 97
> format.
[quoted text clipped - 13 lines]
> perfom mail merge.This means that my excel data source will have a
> blank row in between records for merging to be successful.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

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

keith2816 - 25 Dec 2003 17:13 GMT

I have solved the problem by using the query option of mail merge t
sort my result by 'Name' plus using the filter option as mentione
earlier. This seems to solve my problem but I am wondering what is th
significance of using the sort function as I can pre-sort my excel dat
source in that order prior to mail merging

Reply to this Message

Cindy M -WordMVP- - 26 Dec 2003 09:31 GMT

Hi Keith2816,

I must have been really tired when I answered you before <thunk>

The correct response should have been:
When you filter in Excel, Word will still pick up all the records. You
need to go into Word's query options and set one field that you know
always has entries to "Is not blank". Filtered records in Word are picked
up as "empty records", so setting an option to pick up "not blank" will
leave these out of the mail merge.

> I have solved the problem by using the query option of mail merge to
> sort my result by 'Name' plus using the filter option as mentioned
> earlier. This seems to solve my problem but I am wondering what is the
> significance of using the sort function as I can pre-sort my excel data
> source in that order prior to mail merging.

Often, trying to sort an Excel file coming across by DDE won't work; mail
merge will retain the sort order from Excel. The purpose of having the
sort order in the Query Options is that not every data source has such a
convenient interface as Excel, and not everyone can use Excel :-) If the
data is coming in directly from, say, SQL Server or a FoxPro table the
user won't have any control about how the data is sorted.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

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