Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.docmanagement
Kelley Kelley is offline
external usenet poster
 
Posts: 9
Default Word Mail Merge not picking up filtered data from Excel

Word 2007, Excel 2007, XP

I am trying to use Mail Merge in Word (envelopes) from a large Excel
spreadsheet. The Excel spreadsheet has several columns that have been
filtered. I turn the filter off before saving the spreadsheet (saving the
filtered information). When I merge, ALL the information on the spreadsheet
is coming over - which rather defeats the purpose of filtering the
spreadsheet in the first place.

I can copy the filtered spreadsheet onto a new spreadsheet and then the
merge seems to work, but should I have to do this?

Any ideas? Thanks.
  #2   Report Post  
Posted to microsoft.public.word.docmanagement
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Word Mail Merge not picking up filtered data from Excel

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

Kelley wrote:
Word 2007, Excel 2007, XP

I am trying to use Mail Merge in Word (envelopes) from a large Excel
spreadsheet. The Excel spreadsheet has several columns that have been
filtered. I turn the filter off before saving the spreadsheet (saving the
filtered information). When I merge, ALL the information on the spreadsheet
is coming over - which rather defeats the purpose of filtering the
spreadsheet in the first place.

I can copy the filtered spreadsheet onto a new spreadsheet and then the
merge seems to work, but should I have to do this?

Any ideas? Thanks.

  #3   Report Post  
Posted to microsoft.public.word.docmanagement
Kelley Kelley is offline
external usenet poster
 
Posts: 9
Default Word Mail Merge not picking up filtered data from Excel

Dang. I thought that might be the case (well at least I'm not a totally
moron here!). Thanks so much for your response and time!

"Peter Jamieson" wrote:

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

Kelley wrote:
Word 2007, Excel 2007, XP

I am trying to use Mail Merge in Word (envelopes) from a large Excel
spreadsheet. The Excel spreadsheet has several columns that have been
filtered. I turn the filter off before saving the spreadsheet (saving the
filtered information). When I merge, ALL the information on the spreadsheet
is coming over - which rather defeats the purpose of filtering the
spreadsheet in the first place.

I can copy the filtered spreadsheet onto a new spreadsheet and then the
merge seems to work, but should I have to do this?

Any ideas? Thanks.


Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Merging Filtered Excel Data LFP Mailmerge 1 January 28th 09 10:01 PM
Excel Data Filtered for Unique records George Mailmerge 6 January 14th 09 08:53 PM
Mail Merge Not Picking All Up data Janet A. Thompson Mailmerge 5 December 8th 08 08:40 PM
Why doesn't mail merge show filtered excel worksheets REVA Mailmerge 1 January 24th 06 03:30 PM
How do I do mail merge using filtered data from excel REVA Mailmerge 3 January 24th 06 03:30 PM


All times are GMT +1. The time now is 08:22 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"