Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Data Filtered for Unique records
I am using an Excel 2007 data file which I have filtered for unique records.
I want the file to merge with my Word Template as it shows. However, the filtering is removed when I perform the merge. How can I retain the filtering (I don't want to copy field values to a new worksheet because I still want the formulas to work)?? Thanks in advance! |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Data Filtered for Unique records
I am fairly sure that if you filter "in situ" in Excel, you will only be
able to use the filtered data successfuly in Word merge, directly from Excel, if you a. also sort the data so that all the records you want are right at the beginning of the list b. select the filtered data and create a range name c. re-connect to the Excel workbook from Word and select the range name as the data source rather than the worksheet. However, in that case you would probably be better off if you could simply sort the data so that the data you want is at the beginning, then apply and use a range name. Otherwise, I am not sure you can avoid including records that you do not want. Alternative approaches a a. filter in Word (the facilities in Word are a bit limited, but may be enough b. copy/paste the filtered data into a word document and use that as the data source. However, if you are filtering to eliminate duplicates, I do not think that will work either c. use Word VBA's OpenDataSource method to specify a filter in SQL - typically a SELECT DISTINCT - this can be more flexible than using the Word user interface to do it, but obviously requires that you either know or learn enough VBA and SQL to do it. Perhaps that's enough to suggest that it might be simpler to make a copy of the filtered data in Excel despite the downside. Peter Jamieson http://tips.pjmsn.me.uk George wrote: I am using an Excel 2007 data file which I have filtered for unique records. I want the file to merge with my Word Template as it shows. However, the filtering is removed when I perform the merge. How can I retain the filtering (I don't want to copy field values to a new worksheet because I still want the formulas to work)?? Thanks in advance! |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Data Filtered for Unique records
Hi Peter,
Presumably one could also embed a SKIPIF field in the mailmerge main document, coded with logic that is functionally equivalent to the filter in Excel. -- Cheers macropod [MVP - Microsoft Word] "Peter Jamieson" wrote in message ... I am fairly sure that if you filter "in situ" in Excel, you will only be able to use the filtered data successfuly in Word merge, directly from Excel, if you a. also sort the data so that all the records you want are right at the beginning of the list b. select the filtered data and create a range name c. re-connect to the Excel workbook from Word and select the range name as the data source rather than the worksheet. However, in that case you would probably be better off if you could simply sort the data so that the data you want is at the beginning, then apply and use a range name. Otherwise, I am not sure you can avoid including records that you do not want. Alternative approaches a a. filter in Word (the facilities in Word are a bit limited, but may be enough b. copy/paste the filtered data into a word document and use that as the data source. However, if you are filtering to eliminate duplicates, I do not think that will work either c. use Word VBA's OpenDataSource method to specify a filter in SQL - typically a SELECT DISTINCT - this can be more flexible than using the Word user interface to do it, but obviously requires that you either know or learn enough VBA and SQL to do it. Perhaps that's enough to suggest that it might be simpler to make a copy of the filtered data in Excel despite the downside. Peter Jamieson http://tips.pjmsn.me.uk George wrote: I am using an Excel 2007 data file which I have filtered for unique records. I want the file to merge with my Word Template as it shows. However, the filtering is removed when I perform the merge. How can I retain the filtering (I don't want to copy field values to a new worksheet because I still want the formulas to work)?? Thanks in advance! |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Data Filtered for Unique records
Yes, that's also an option, quite probably in this case. If the user is
trying to deduplicate then it requires that the data source is sorted. Personally I tend to steer clear of { SKIPIF } on the grounds that a. it was "deprecated" by Microsoft several versions ago - or at the very least, its use was discouraged b. it doesn't really do what a user might imagine it does given the name - i.e. it doesn't simply skip records in the data source; it skips records in the data sorce /and/ causes Word to finish processing the current copy of the mail merge main doc. and start a new one. Which may be exactly what you want in some cases but will almost certainly screw you up in, e.g., label merges. However, that's just my view, and there are certainly cases where it's probably the simplest solution. The introduction of the OOXML standards certainly makes you wonder what Microsoft will do with its "field language" in future versions. If it wants to conform to the standard, it would probably have to change the way fields work, because AFAICS there are certain areas in which the standard conflicts with current reality (for example I do not think it describes the field parameter syntax that allows use of { QUOTE 13 } to insert a CR, and I think it specifies that { DATABASE } always inserts a table, whereas in fact it does not if the table would only consist of one cell. However, I'd have to check the standard much more thoroughly to be sure. However, since not conforming to the standard would screw a lot of applications up, and modifying the code would probably be expensive, it seems much more likely that current field behaviour will be ossified and complaints that Word is non-conformant will be dismissed somehow or other. Peter Jamieson http://tips.pjmsn.me.uk macropod wrote: Hi Peter, Presumably one could also embed a SKIPIF field in the mailmerge main document, coded with logic that is functionally equivalent to the filter in Excel. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Data Filtered for Unique records
Hi Peter,
Whew! That rejoinder is rather more than I bargained for. However, FWIW your comment about "the field parameter syntax that allows use of { QUOTE 13 } to insert a CR" is wrong. If you try it, Word will generate the error message "Error! Cannot insert return character." Still, there remains the question of how one applies the OOXML standards to using { QUOTE 09 } to insert a tab, { QUOTE 11 } to insert a line feed, { QUOTE 12 } to insert a page break, { QUOTE 14 } to insert a column break, and so on. -- Cheers macropod [MVP - Microsoft Word] "Peter Jamieson" wrote in message ... Yes, that's also an option, quite probably in this case. If the user is trying to deduplicate then it requires that the data source is sorted. Personally I tend to steer clear of { SKIPIF } on the grounds that a. it was "deprecated" by Microsoft several versions ago - or at the very least, its use was discouraged b. it doesn't really do what a user might imagine it does given the name - i.e. it doesn't simply skip records in the data source; it skips records in the data sorce /and/ causes Word to finish processing the current copy of the mail merge main doc. and start a new one. Which may be exactly what you want in some cases but will almost certainly screw you up in, e.g., label merges. However, that's just my view, and there are certainly cases where it's probably the simplest solution. The introduction of the OOXML standards certainly makes you wonder what Microsoft will do with its "field language" in future versions. If it wants to conform to the standard, it would probably have to change the way fields work, because AFAICS there are certain areas in which the standard conflicts with current reality (for example I do not think it describes the field parameter syntax that allows use of { QUOTE 13 } to insert a CR, and I think it specifies that { DATABASE } always inserts a table, whereas in fact it does not if the table would only consist of one cell. However, I'd have to check the standard much more thoroughly to be sure. However, since not conforming to the standard would screw a lot of applications up, and modifying the code would probably be expensive, it seems much more likely that current field behaviour will be ossified and complaints that Word is non-conformant will be dismissed somehow or other. Peter Jamieson http://tips.pjmsn.me.uk macropod wrote: Hi Peter, Presumably one could also embed a SKIPIF field in the mailmerge main document, coded with logic that is functionally equivalent to the filter in Excel. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Data Filtered for Unique records
Yes!
While we're here, another FWIW for anyone who cares about such stuff... I notice that some of the field codes are specified as "transitional" in OOXML - AUTONUM, AUTONUMLEGAL, AUTONUMOUTLINE, BARCODE, BIDIOUTLINE, EQ and INFO. A number of fields that are actually recognised by Word 2007/2008 are not even mentioned in "transitional" - I don't know of any except the following: ADDIN (could well be used by third-party products, especially for merge-type operations. The rather similar PRIVATE is retained. CONTACT (MAC only - inserts an entry from the Mac Office Address book. CONTROL (i.e. an ActiveX (form) control) Not a very helpful field IMO) DDE (I doubt if even the contact management packages that used to use this and DDEAUTO still do) DDEAUTO EMBED (i.e. I assume that this can be specified another way in XML) GLOSSARY (replaced by AUTOTEXT but still functions) HTMLCONTROL (form field in Web page view) IMPORT (old version of INCLUDETEXT - still functions I think) INCLUDE (old version of INCLUDEPICTURE - still functions I think) PLACEHOLDER (Mac only - I think this is the field whose VBA ID number conflicts with ADDRESSBLOCK, which is not yet implemented on Mac. Lucky VBA went, at least temporarily in Word 2008 and is irrelevant for XML!) SUBSCRIBER (Mac only, for the Mac publish/subscriber facility, pretty sure even the facility is no longer used in MACOSX, at least not extensively And MacWord does not currently implement GREETINGLINE and perhaps some others. Don't think I'll be visiting this particular area for some time to come! Peter Jamieson http://tips.pjmsn.me.uk macropod wrote: Hi Peter, Whew! That rejoinder is rather more than I bargained for. However, FWIW your comment about "the field parameter syntax that allows use of { QUOTE 13 } to insert a CR" is wrong. If you try it, Word will generate the error message "Error! Cannot insert return character." Still, there remains the question of how one applies the OOXML standards to using { QUOTE 09 } to insert a tab, { QUOTE 11 } to insert a line feed, { QUOTE 12 } to insert a page break, { QUOTE 14 } to insert a column break, and so on. |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Data Filtered for Unique records
CONTACT (MAC only - inserts an entry from the Mac Office Address book.
CONTROL (i.e. an ActiveX (form) control) Not a very helpful field IMO) should be... CONTACT (MAC only - inserts an entry from the Mac Office Address book. Not a very helpful field IMO) CONTROL (i.e. an ActiveX (form) control) Peter Jamieson http://tips.pjmsn.me.uk Peter Jamieson wrote: Yes! While we're here, another FWIW for anyone who cares about such stuff... I notice that some of the field codes are specified as "transitional" in OOXML - AUTONUM, AUTONUMLEGAL, AUTONUMOUTLINE, BARCODE, BIDIOUTLINE, EQ and INFO. A number of fields that are actually recognised by Word 2007/2008 are not even mentioned in "transitional" - I don't know of any except the following: ADDIN (could well be used by third-party products, especially for merge-type operations. The rather similar PRIVATE is retained. CONTACT (MAC only - inserts an entry from the Mac Office Address book. CONTROL (i.e. an ActiveX (form) control) Not a very helpful field IMO) DDE (I doubt if even the contact management packages that used to use this and DDEAUTO still do) DDEAUTO EMBED (i.e. I assume that this can be specified another way in XML) GLOSSARY (replaced by AUTOTEXT but still functions) HTMLCONTROL (form field in Web page view) IMPORT (old version of INCLUDETEXT - still functions I think) INCLUDE (old version of INCLUDEPICTURE - still functions I think) PLACEHOLDER (Mac only - I think this is the field whose VBA ID number conflicts with ADDRESSBLOCK, which is not yet implemented on Mac. Lucky VBA went, at least temporarily in Word 2008 and is irrelevant for XML!) SUBSCRIBER (Mac only, for the Mac publish/subscriber facility, pretty sure even the facility is no longer used in MACOSX, at least not extensively And MacWord does not currently implement GREETINGLINE and perhaps some others. Don't think I'll be visiting this particular area for some time to come! Peter Jamieson http://tips.pjmsn.me.uk macropod wrote: Hi Peter, Whew! That rejoinder is rather more than I bargained for. However, FWIW your comment about "the field parameter syntax that allows use of { QUOTE 13 } to insert a CR" is wrong. If you try it, Word will generate the error message "Error! Cannot insert return character." Still, there remains the question of how one applies the OOXML standards to using { QUOTE 09 } to insert a tab, { QUOTE 11 } to insert a line feed, { QUOTE 12 } to insert a page break, { QUOTE 14 } to insert a column break, and so on. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Merge - Excel Records to Word Table | Mailmerge | |||
Not merging all records from an Excel data source | Mailmerge | |||
filter un-unique records in word | Microsoft Word Help | |||
How do I do mail merge using filtered data from excel | Mailmerge | |||
mail merge with non-unique records | Mailmerge |