Mail merge and Excel filtering

[BeetleB]

At home, I am working on an XP operating system with Work and Excel versions.
Microsoft Office Word 2003 and Microsoft Office Word 2003. I have SP2.


I encountered a problem last evening. I keep the mailing information of an
organization of over 500 members. The database for the org is an Excel
spreadsheet. Since 2006, I have been using Mail Merge in Word to generate
mail labels for the month mailings. All has worked very well. Last night,
I browsed in the merge steps to find my workbook. I opened it. The
spreadsheet named MEPN appeared along with a spreadsheet MEPN_ which does not
exist in the workbook. (It turns out that last month, I used filtering in
Excel to get some special lists. ) When I selected, the spreadsheet MEPN as
I would normally do, I was met with an emtpy Tables screen --- instead of
the usual possibility to filter the MEPN worksheet items. Pressing OK, the
message said that my table (presumably MEPN) could not be opened.

Does using Excel filtering disable my use of Mail Merge? Does it corrupt
the spreadsheet for use in Mail Merge? I worked with the spreadsheet ---
turning on and off the filtering in Excel and got the same results in Mail
Merge. I even copied the worksheet to another new workbook and got similar
results. The table could not be opened. What I believe does work as a
workaround is to copy the content and paste it in a new workbook. Then I use
that new workbook with Mail Merge. Is there anyway to redeem my original
worksheet so that I nullify the results of the filtering? I have done some
searching. Is this problem discussed somewhere on the Web?

Thanks.

BettleB

 

[Doug Robbins - Word MVP]

I don't believe that filtering from Word should have any impact on the data
source.

You may try selecting Options from the Tools menu in Word and then go to the
General tab and check the Confirm conversions at open item and see if
changing the method of connecting to the data source (for which you will
then be given the option when you go to select the data source) has any
influence on the behaviour.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

Does using Excel filtering disable my use of Mail Merge?

I have certainly seen this, but have obviously not worked enough with Excel
lists myself to be able to replicate the problem. I will try again when I
get some time. It certainly isn't merely the presence of a list that causes
the problem, and it's possible to do quite a lot to the list without any
problems arising.

Does it corrupt
the spreadsheet for use in Mail Merge?

I don't know exactly what it does, but my guess is that it does something
that causes the Jet/OLE DB provider that Word uses to get the data to fail
in some way. It is certainly worth trying to use DDE to connect to the data,
if it is in the first sheet in your workbook (check Word
Tools|Options|General|COnfirm conversions at open, go through the process of
connecting to the sheet again, and choose the DDE option when offered.

A few questions for you (they may help me to avoid going down blind alleys):
a. what sort of things did you do with your list? Filtering? Sorting?
b. did you use the data form to alter the list data?
c. did you connect to any external databases?
d. was the list at the top left of the sheet, or somewhere else on your
sheet?
e. how did you "remove" the list? Did you convert it to a range?

(Obviously it would be great to see the workbook you have but in this case
it sounds unlikely you would be able to provide it).

 

[BeetleB]

a. during this year of work with the workbook, I have mainly added new rows
(records) to the main member spreadsheet. Yes, a few sorts and, as I said,
last month I created some lists by filtering based on paying and non-paying
members of the org.
b. no.
c. no.
d. my problem occurred with auto-filtering turned off. The spreadsheet is
usually fully displayed. (As a thought, I moved the member spreadsheet to
the extreme left (sheet 1 position) for testing.
e. If I understand - "remove" the list, I created the lists in October by
using auto-filter. When done, I just turned off, auto filter.

Now when I use merge to build my labels, I see this MEPN_ spreadsheet. If I
turn on auto-filter and test a merge, sure enough, the name becomes something
like MEPN_Filter database or something like that -- that convinces me that
filtering has left a residue.

I set the Confirm conversion option in Word and tested tonight. Again - the
spreadsheet could not be opened. I used all three conversion methods
suggested -- none work.

Another window comes up using DDE giving me choices of Entire Spreadsheet or
_Filter Database. More evidence.

Thanks for trying to help. As I said I have a workaround that is a little
clumsy.
I appreciate your time. The other gentleman also suggested using that
option.

 

[Peter Jamieson]

Yes, there is no doubt that filtering leaves a "residue."

I've done a little bit of research but not yet enough to answer your
question. So far...

1. When you set up the filter, Excel creates a range called
thesheetname!_FilterDatabase.

2. If the sheet is open when you connect from Word (or even to itself, from
Excel), you see this name listed as a "TABLE", except there is a $ instead
of the !, i.e. you see thesheetname$_FilterDatabase. However, if the sheet
is closed when you connect from Word, you see the name thesheetname$_

3. When you try to connect from Word (or Excel), Word/Excel can use the
thesheetname$_FilterDatabase table as a data source. But they cannot use
thesheetname$_ as a data source.

4. However, I still have not been able to work out what makes existing
sheets inaccessible from Word. But maybe you could try two experiments:
a. when you try to connect from Word (using OLE DB, which is the default
connection method), does it make a difference whether or not the sheet is
open in Excel?
b. if you create a new blank Excel workbook, then use Excel Data|Import
External Data|Import Data and select the "problem" workbook, can you import
the data? (Also try with the problem workbook opened and closed). FWIW I
would expect the same problem as both Word and Excel are using the same OLE
DB provider to get their data. But Excel is sometimes rather better at doing
that, so it seems worth trying.