Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
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
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
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
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging Filtered Excel Data | Mailmerge | |||
Excel Data Filtered for Unique records | Mailmerge | |||
Mail Merge Not Picking All Up data | Mailmerge | |||
Why doesn't mail merge show filtered excel worksheets | Mailmerge | |||
How do I do mail merge using filtered data from excel | Mailmerge |