Questions

Word 2003 Mail Merge with Excel - Advance Filter problems

+
0 Votes
Locked

Word 2003 Mail Merge with Excel - Advance Filter problems

Blasdale
On an XP system I set up a mailing label document in Word 2003 that uses an Excel spreadsheet as data source. In order to suppress certain rows, I used the "advance filter" option on the recipients list. Whenever I save the labels document, or even if I just open up the recipients list and click OK, spurious lines are added to the advance filter. If I repeat either of these actions, further spurious lines ae added to the filter. The spurious lines are such that the filtering condition does not do what I originally wanted it to do. For example, if I have a condition such as: "List Not Equal to ABC" the system adds "Or List Is blank". I previously used this same labeling setup on a Windows 98 - Word 97 setup, and things worked fine. Thinking there might be a conversion issue, I re-built the entire label document from scratch, but it made no difference. Can anyone throw any light on this?
  • +
    0 Votes

    This might help you or point you in the right direction.

    http://www.mvps.org/dmcritchie/excel/mailmerg.htm

    Please post back if you have anymore problems or questions.

    +
    0 Votes
    Blasdale

    Peconet,
    Hyv?? p?iv??!
    Thanks for the link you sent. I looked through it and also through some of the links within it. I had not seen some that material, and it will useful in general. Unfortunately I did not see anything relating to the problem I have. There was quite a bit about the advanced filter in Excel directly, but not about the advanced filter in Word's "recipients" pop-up. I believe this is a bug of some kind, although I don't understand why more people have not encountered it. Perhaps there's something else on my system that's causing it, although I have tried it on both my home system and my laptop at work. It fails in the same way. I will try bringing my MS Office completely up to date to see if that solves the problem. If not, I will need to find an alternative to Word's mail merge.
    Thanks again, JB.

    +
    0 Votes
    ThumbsUp2

    Try setting the print area of the spreadsheet so there are no blank lines below the data and then saving. If mailmerge thinks that it should look at the entire document to find records, it could be adding the extra filter because of the setting in Word to ignore records which are blank.

    I don't understand why your mailmerge would not work right though, when blank lines are suppressed. Do you have something in there actually looking FOR a blank line?

    +
    0 Votes
    Blasdale

    I seem now to have partially solved the problem, but I believe there is a serious bug in Word 2003 mail merge. As previously noted, Word seemed to add extra lines into my advanced filter specification. My original spec looked like: "IF List Is blank AND Suppress Not equal to X". Word added two extra "Is blank" conditions that actually caused the wrong merge results to be generated. Since I was trying to filter out lines where List = ABC, I decided to try using "contains" conditions, i.e. something like: "IF List Does not contain ABC AND Suppress Does not contain X". This now produced the correct merge results. However, when I later redisplayed the filter dialog box, my filter conditions were not displayed - the entire dialog box was empty. However, it turned out that the filter condition WAS actually saved with the label document because I could re-run the merge and still get correct results. So, we have the following mystery: When I used the filter condition containing "Is blank", Word displayed the condition but added stuff of its own. When I used the filter condition with "contains", Word displayed the filter dialog box as empty whenever I redisplayed it (merge results were correct though). I did not have this experience using the older Word 97; I also tried Open Office's mail merge (with my MS XLS spreadsheet), and things worked perfectly. Thus, I suspect a Word 2003 bug.
    JB

    +
    0 Votes
    m.bell

    I've been struggling with the same problem. I notice that it's been almost a year since your post. Have you had any progress in correcting the problem?

    +
    0 Votes
    rjonathon

    I've been running into this problem a lot as well, but it looks like the Original Poster's solution works. It seems that Word 2003 does not like the "is blank" filter, especially when combined with a 2nd filter criteria. This might have something to do with the original source data, since I don't experience this problem with all spreadsheets.

    At any rate, OP's solution works... for my spreadhseet, I have a column that indicates whether or not a person has received a mailing with an X. So instead of saying "'Mailed to' is blank", I followed OP's advice and used "'Mailed to' does not contain 'X'".... works like a charm.

    Hope this bug has been fixed in Word 07!

    +
    0 Votes
    jondoig

    We're still on Word 2003 and I have this problem so thanks for posting about it so I could stop hitting my head against the same brick wall.

    FYI my filter was of the form:
    field1 does not equal value1
    AND
    field2 equals value2

    It kept adding spurious lines, and even when I went back and thought I'd corrected it, the merge output had records that should have been filtered.

    My workaround is to clobber the Excel source with a pre-filtered dataset for each merge run, so I can run each merge with no MSWord filter at all. Painful but a solution at least.

  • +
    0 Votes

    This might help you or point you in the right direction.

    http://www.mvps.org/dmcritchie/excel/mailmerg.htm

    Please post back if you have anymore problems or questions.

    +
    0 Votes
    Blasdale

    Peconet,
    Hyv?? p?iv??!
    Thanks for the link you sent. I looked through it and also through some of the links within it. I had not seen some that material, and it will useful in general. Unfortunately I did not see anything relating to the problem I have. There was quite a bit about the advanced filter in Excel directly, but not about the advanced filter in Word's "recipients" pop-up. I believe this is a bug of some kind, although I don't understand why more people have not encountered it. Perhaps there's something else on my system that's causing it, although I have tried it on both my home system and my laptop at work. It fails in the same way. I will try bringing my MS Office completely up to date to see if that solves the problem. If not, I will need to find an alternative to Word's mail merge.
    Thanks again, JB.

    +
    0 Votes
    ThumbsUp2

    Try setting the print area of the spreadsheet so there are no blank lines below the data and then saving. If mailmerge thinks that it should look at the entire document to find records, it could be adding the extra filter because of the setting in Word to ignore records which are blank.

    I don't understand why your mailmerge would not work right though, when blank lines are suppressed. Do you have something in there actually looking FOR a blank line?

    +
    0 Votes
    Blasdale

    I seem now to have partially solved the problem, but I believe there is a serious bug in Word 2003 mail merge. As previously noted, Word seemed to add extra lines into my advanced filter specification. My original spec looked like: "IF List Is blank AND Suppress Not equal to X". Word added two extra "Is blank" conditions that actually caused the wrong merge results to be generated. Since I was trying to filter out lines where List = ABC, I decided to try using "contains" conditions, i.e. something like: "IF List Does not contain ABC AND Suppress Does not contain X". This now produced the correct merge results. However, when I later redisplayed the filter dialog box, my filter conditions were not displayed - the entire dialog box was empty. However, it turned out that the filter condition WAS actually saved with the label document because I could re-run the merge and still get correct results. So, we have the following mystery: When I used the filter condition containing "Is blank", Word displayed the condition but added stuff of its own. When I used the filter condition with "contains", Word displayed the filter dialog box as empty whenever I redisplayed it (merge results were correct though). I did not have this experience using the older Word 97; I also tried Open Office's mail merge (with my MS XLS spreadsheet), and things worked perfectly. Thus, I suspect a Word 2003 bug.
    JB

    +
    0 Votes
    m.bell

    I've been struggling with the same problem. I notice that it's been almost a year since your post. Have you had any progress in correcting the problem?

    +
    0 Votes
    rjonathon

    I've been running into this problem a lot as well, but it looks like the Original Poster's solution works. It seems that Word 2003 does not like the "is blank" filter, especially when combined with a 2nd filter criteria. This might have something to do with the original source data, since I don't experience this problem with all spreadsheets.

    At any rate, OP's solution works... for my spreadhseet, I have a column that indicates whether or not a person has received a mailing with an X. So instead of saying "'Mailed to' is blank", I followed OP's advice and used "'Mailed to' does not contain 'X'".... works like a charm.

    Hope this bug has been fixed in Word 07!

    +
    0 Votes
    jondoig

    We're still on Word 2003 and I have this problem so thanks for posting about it so I could stop hitting my head against the same brick wall.

    FYI my filter was of the form:
    field1 does not equal value1
    AND
    field2 equals value2

    It kept adding spurious lines, and even when I went back and thought I'd corrected it, the merge output had records that should have been filtered.

    My workaround is to clobber the Excel source with a pre-filtered dataset for each merge run, so I can run each merge with no MSWord filter at all. Painful but a solution at least.