Need a macro to create Geographic Listing of US States and Countries...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg Mouning - 20 Apr 2004 00:33 GMT

Hi,

I'm back with another macro mail merge challenge. I use Microsoft Word
97 on a Windows/NT machine. I need to perform a Catalogue mail merge
using an Excel spread sheet as the data. The resulting Word merge
document needs to yield a Geographical listing containing a group of
names sorted in Alphabetical order by last name. Each spreadsheet
entry contains a firstname, lastname, city, state, zip and country
field.

The problems are, the states are abbreviated and I need them spelled
out as follows:

AK = Arkansas, CT=Connecticut, OH=Ohio, and VA=Virginia.

I also need to group all the names together, and place the names of
the US states and a few other country names above the group of names
for which they belong. For example:

Arkansas
Bill Clinton

Connecticut
George W. Bush

Ohio
James Garfield
Ulysses Grant
Warren Harding
Benjamin Harrison
Rutherford Hayes
William McKinley
William Taft

Virginia
William Henry Harrison
Thomas Jefferson
James Madison
James Monroe
Zachary Taylor
John Tyler
George Washington

As always, any assistance you can provide is greatly appreciated.

Regards,
Greg

Reply to this Message

Graham Mayor - 20 Apr 2004 09:37 GMT

I guess the easiest way to complete the states would be to create a lookup
table in Excel to derive a new column containing the full name of the state.
The alternative of using umpteen conditional fields all on the same row
doesn't bear thinking about eg
{IF {Mergefield State} = AK "Arkansas"}{IF {Mergefield State} = CT
"Connecticut"} etc for each of the states. (You would be able to save this
as an autotext which is a small consolation).
The Alphabetical listing will require the Excel columns to be sorted first
on State, next on Name.
You can then use a special merge as described by Cindy at
http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#ComplexMerg
to prepare the list.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor - Word MVP

Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

> Hi,
>
[quoted text clipped - 43 lines]
> Regards,
> Greg

Reply to this Message

Doug Robbins - Word MVP - 21 Apr 2004 00:10 GMT

Answered in microsoft.public.word.word97vba. Please do not post the same
question separately to multiple newsgroups.

Signature

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

> Hi,
>
[quoted text clipped - 43 lines]
> Regards,
> Greg

Reply to this Message

johnm - 21 Apr 2004 09:47 GMT

Greg : the following Excel macro should expand your state
codes - amend it to set the range to the column in which
you have your existing state abbreviation and edit the
data lines to fit

Sub CountyCodeReplacement()
'
' CountyCodeReplacement Macro
' Macro recorded 14/05/2001 by johnm
'

'
Range("G:G").Select
Cells.Replace What:="AV", Replacement:="Avon",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True
Cells.Replace What:="BD", Replacement:="Bedfordshire",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True
Cells.Replace What:="BF", Replacement:="BFPO",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True
Cells.Replace What:="BK", Replacement:="Berkshire",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True
Cells.Replace What:="BU",
Replacement:="Buckinghamshire", LookAt:=xlWhole,_
SearchOrder:=xlByRows, MatchCase:=True
Cells.Replace What:="CB",
Replacement:="Cambridgeshire", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True

>-----Original Message-----
>Hi,
[quoted text clipped - 45 lines]
>Greg
>.

Reply to this Message

Rate this thread: