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

[Greg Mouning]

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

 

[Doug Robbins - Word MVP]

Hi Greg,

Here's a list of all of the abbreviations from
http://www.usps.com/ncsc/lookups/usps_abbreviations.html

State/Possession
Abbreviation

ALABAMA
AL

ALASKA
AK

AMERICAN SAMOA
AS

ARIZONA
AZ

ARKANSAS
AR

CALIFORNIA
CA

COLORADO
CO

CONNECTICUT
CT

DELAWARE
DE

DISTRICT OF COLUMBIA
DC

FEDERATED STATES OF MICRONESIA
FM

FLORIDA
FL

GEORGIA
GA

GUAM
GU

HAWAII
HI

IDAHO
ID

ILLINOIS
IL

INDIANA
IN

IOWA
IA

KANSAS
KS

KENTUCKY
KY

LOUISIANA
LA

MAINE
ME

MARSHALL ISLANDS
MH

MARYLAND
MD

MASSACHUSETTS
MA

MICHIGAN
MI

MINNESOTA
MN

MISSISSIPPI
MS

MISSOURI
MO

MONTANA
MT

NEBRASKA
NE

NEVADA
NV

NEW HAMPSHIRE
NH

NEW JERSEY
NJ

NEW MEXICO
NM

NEW YORK
NY

NORTH CAROLINA
NC

NORTH DAKOTA
ND

NORTHERN MARIANA ISLANDS
MP

OHIO
OH

OKLAHOMA
OK

OREGON
OR

PALAU
PW

PENNSYLVANIA
PA

PUERTO RICO
PR

RHODE ISLAND
RI

SOUTH CAROLINA
SC

SOUTH DAKOTA
SD

TENNESSEE
TN

TEXAS
TX

UTAH
UT

VERMONT
VT

VIRGIN ISLANDS
VI

VIRGINIA
VA

WASHINGTON
WA

WEST VIRGINIA
WV

WISCONSIN
WI

WYOMING
WY


Military "State"
Abbreviation

Armed Forces Africa
AE

Armed Forces Americas
(except Canada)
AA

Armed Forces Canada
AE

Armed Forces Europe
AE

Armed Forces Middle East
AE

Armed Forces Pacific
AP



You could put this in an Excel Spreadsheet and use one of the Lookup
functions to populate another column in your data source with the full name
of the State, or I would do it in Access with a select query and also use an
Access report to get the grouping that you want. Alternatively if you
create a catalog mailmerge into a table in Word with the State in the first
column and the other data in columns 2 thru whatever, and then you run the
following macro over that document, it will create a table organised as you
want it

' Macro to create multiple items per condition from a directory type
mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat <> tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i


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