Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
George George is offline
external usenet poster
 
Posts: 77
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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

Posting Rules

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

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Merge - Excel Records to Word Table John McGhie [MVP Word, Word Mac] Mailmerge 1 May 14th 07 07:54 AM
Not merging all records from an Excel data source CMEC Mailmerge 3 December 8th 06 07:41 PM
filter un-unique records in word chmurthy Microsoft Word Help 2 October 27th 06 07:34 AM
How do I do mail merge using filtered data from excel REVA Mailmerge 3 January 24th 06 03:30 PM
mail merge with non-unique records tjb Mailmerge 3 December 13th 05 06:30 AM


All times are GMT +1. The time now is 09:27 AM.

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"