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.
--
Peter Jamieson
http://tips.pjmsn.me.uk
"BeetleB" wrote in message
...
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" wrote:
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).
--
Peter Jamieson
http://tips.pjmsn.me.uk
"BeetleB" wrote in message
...
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