I can copy the filtered spreadsheet onto a new spreadsheet and then the
merge seems to work, but should I have to do this?
Let me put it this way:
1. I think it would be very useful if there was a simpler way to use a
filtered list from Excel as a datasource
2. Unfortunately, I do not think there is an obviously simpler way.
FWIW one way to define a new area in Excel that is not a complete
worksheet but which can be used as a mail merge data source has been to
select the appropriate block of cells and define a range name. Then,
when Word tries to open the Workbook, the range name is presented as one
of the "tables" (These "tables" are quite different from the new "Table"
feature in Excel 2007, BTW). Unfortunately, if you create a range name
for a filtered list,
a. when Word gets the data, it still sees all the rows anyway
b. it's often actually quite hard to select just the cells you need.
So can anything be done using the nice new Excel 2007 Table facility
that lets you do filtering and "Structured references"? Well, not as far
as I can see - although it defines a name for the table, as far as I can
see these names are not regarded as "tables" by the OLE DB provider that
retrieves Excel data on behalf of Word. Nor does the obvious trick of
setting your own range name to the table name result in such a table
appearing. If anyone knows a way to do /that/, that really would be
useful if the range name's definition also automatically adjusted when
rows were inserted into or deleted from the table.
The only other approach that actually achieves anything close to useful
is to connect to your data using the old DDE method, and even that
approach is tricky in Word 2007. The DDE method can only get data from
the first sheet of the workbook, and as far as I can tell, you cannot
always actually specify the "Entire Spreadsheet" as the data source as
you normally would with DDE - if not, you have to specify a range name
or a range in R1C1 format.
If you then look at Edit Recipients, you can see that the filtered out
rows are all blank, so you can use Word's filter mechanism to exclude
rows where (say) the first field is blank).
If you really want to try that, then
a. you have to set Word up so it can use DDE - go to Word Office
Button->Word Options->Advanced->General (near the bottom of the list)
and check "Confirm file format conversion on open"
b. after you have selected the data source, Word pops up an additional
dialog box titled Confirm Data Source. If the workbook is a .xls you
should see a DDE option in there. If it's a .xlsx or .xlsm, you have to
check Show All, then look for the Excel via DDE option.
c. I leave you to work out how to do the filtering in Word.
I think I'd rather copy/paste the rows I wanted in Excel, as you are doing.
Peter Jamieson
http://tips.pjmsn.me.uk