Macro to select text maching specified conditions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Josha - 05 Sep 2006 01:40 GMT

I need to create a macro that will start at the begining of a Word document
and select all sections of text that are in ALLCAPS. It then must change the
instances of all caps to title or proper caps. However there are specific two
letter codes that must remain as all caps. Is this possible?

Basically, a mail merge that uses a database query as the datasource is
importing address and name details in allcaps. To maintain professionalism,
it is desired that these items are in proper caps. However, the letter
contains two letter 'class codes' that must remain in all caps (eg. BI, FH,
LF, NM).

I am only just learning VB and can only get the macro the select the whole
document and change it to proper caps. Manually selecting the required text
is not an option because the merge contains many records. This would take too
long.

My other thought was to use a macro to select specific line/column reference
to select the required text, then change it to proper caps, then repeat for
each page. I dont know how to do this either and it is not ideal as the
template letter may change, invalidating the references.

Any help would be much appreciated.

Reply to this Message

Jezebel - 05 Sep 2006 02:06 GMT

This is actually *very* hard to do well. The capitalisation rules for names
(in particular) and addresses are startingly complex (check any style
guide), and trying to do this automatically is likely to result in
embarrassment. Although most words are straightforward, in any sizable
mailing list you get a fair sprinkling of exceptions: "van Neuman" "M'Benga"
"ffolkes" "O'Reilly" etc. Addresses likewise contain plenty of oddball
strings that will end up looking odd if you globally change case.

A better approach (usually) is to run your query into Excel, use the
Proper() function, then manually inspect the result using search and sort to
find the anomalies.

>I need to create a macro that will start at the begining of a Word document
> and select all sections of text that are in ALLCAPS. It then must change
[quoted text clipped - 26 lines]
>
> Any help would be much appreciated.

Reply to this Message

Josha - 05 Sep 2006 03:15 GMT

That being said, is it possible without respect to those exceptions. Even if
you can advise how to limit a select command to text matching a criteria,
that would be useful.

> This is actually *very* hard to do well. The capitalisation rules for names
> (in particular) and addresses are startingly complex (check any style
[quoted text clipped - 38 lines]
> >
> > Any help would be much appreciated.

Reply to this Message

Jezebel - 05 Sep 2006 04:05 GMT

Well certainly the macro part of it is not hard. Tos tart, you'll need to
write out the list of conditions that you're trying to match (ie, based on
how the data is structured and how you expect the codes to recognise the
fields to be converted. In respect of your two-letter codes, it might be
simpler to ignore them initially, then convert them back to upper case
afterwards.

But truly, I've done a lot of this work, and the approach you're taking is
the path to strabismus, insanity, and death.

> That being said, is it possible without respect to those exceptions. Even
> if
[quoted text clipped - 51 lines]
>> >
>> > Any help would be much appreciated.

Reply to this Message

John Nurick - 05 Sep 2006 06:57 GMT

Hi Josha (and Jezebel PMFJI)

As Jezebel says it's much easier to work in Access or Excel where the
data is still structured into fields or cells. That way there's no
difficulty distinguishing between class codes and the rest.

One possibility is to use a specialist product to sort out the
capitalisation of the names (e.g. Splitter or NameParser from
www.infoplan.com.au). These don't do a perfect job (no automatic system
can) but they produce better results than almost any home-rolled code.

>I need to create a macro that will start at the begining of a Word document
>and select all sections of text that are in ALLCAPS. It then must change the
[quoted text clipped - 18 lines]
>
>Any help would be much appreciated.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message