Excel Data Filtered for Unique records

[George]

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!

 

[Peter Jamieson]

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 are:
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

 

[macropod]

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.

 

[Peter Jamieson]

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]

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.

 

[Peter Jamieson]

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

 

[Peter Jamieson]

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