Find/Replace for Excel

Scripting Forums AppleScript | Mac OS X
#1

Hi, I wrote this little script that will find and replace US States. I was wondering if someone can tell if there is an cleaner way of coding this script.

property two_dots : ":"

display dialog "Enter the column letter" default answer "" buttons {"Enter"} default button 1
copy the result as list to {column_letter, "Enter"}

tell application "Microsoft Excel"
	set the targetRange to (column_letter & two_dots & column_letter)
	activate
	replace range targetRange what "Alabama" replacement "AL"
	replace range targetRange what "Alaska" replacement "AK"
	replace range targetRange what "American Samoa" replacement "AS"
	replace range targetRange what "Arizona" replacement "AZ"
	replace range targetRange what "Arkansas" replacement "AR"
	replace range targetRange what "California" replacement "CA"
	replace range targetRange what "Colorado" replacement "CO"
	replace range targetRange what "Connecticut" replacement "CT"
	replace range targetRange what "Delaware" replacement "DE"
	replace range targetRange what "District of Columbia" replacement "DC"
	replace range targetRange what "Federated States of Micronesia" replacement "FM"
	replace range targetRange what "Florida" replacement "FL"
	replace range targetRange what "Georgia" replacement "GA"
	replace range targetRange what "Guam" replacement "GU"
	replace range targetRange what "Hawaii" replacement "HI"
	replace range targetRange what "Idaho" replacement "ID"
	replace range targetRange what "Illinois" replacement "IL"
	replace range targetRange what "Indiana" replacement "IN"
	replace range targetRange what "Iowa" replacement "IA"
	replace range targetRange what "Kansas" replacement "KS"
	replace range targetRange what "Kentucky" replacement "KY"
	replace range targetRange what "Louisiana" replacement "LA"
	replace range targetRange what "Maine" replacement "ME"
	replace range targetRange what "Marshall Islands" replacement "MH"
	replace range targetRange what "Maryland" replacement "MD"
	replace range targetRange what "Massachusetts" replacement "MA"
	replace range targetRange what "Michigan" replacement "MI"
	replace range targetRange what "Minnesota" replacement "MN"
	replace range targetRange what "Mississippi" replacement "MS"
	replace range targetRange what "Missouri" replacement "MO"
	replace range targetRange what "Montana" replacement "MT"
	replace range targetRange what "Nebraska" replacement "NE"
	replace range targetRange what "Nevada" replacement "NV"
	replace range targetRange what "New Hampshire" replacement "NH"
	replace range targetRange what "New Jersey" replacement "NJ"
	replace range targetRange what "New Mexico" replacement "NM"
	replace range targetRange what "New York" replacement "NY"
	replace range targetRange what "North Carolina" replacement "NC"
	replace range targetRange what "North Dakota" replacement "ND"
	replace range targetRange what "Northern Mariana Islands" replacement "MP"
	replace range targetRange what "Ohio" replacement "OH"
	replace range targetRange what "Oklahoma" replacement "OK"
	replace range targetRange what "Oregon" replacement "OR"
	replace range targetRange what "Palau" replacement "PW"
	replace range targetRange what "Pennsylvania" replacement "PA"
	replace range targetRange what "Puerto Rico" replacement "PR"
	replace range targetRange what "Rhode Island" replacement "RI"
	replace range targetRange what "South Carolina" replacement "SC"
	replace range targetRange what "South Dakota" replacement "SD"
	replace range targetRange what "Tennessee" replacement "TN"
	replace range targetRange what "Texas" replacement "TX"
	replace range targetRange what "Utah" replacement "UT"
	replace range targetRange what "Vermont" replacement "VT"
	replace range targetRange what "Virgin Islands" replacement "VI"
	replace range targetRange what "Virginia" replacement "VA"
	replace range targetRange what "Washington" replacement "WA"
	replace range targetRange what "West Virginia" replacement "WV"
	replace range targetRange what "Wisconsin" replacement "WI"
	replace range targetRange what "Wyoming" replacement "WY"
end tell
#2

Hi,

there’s nothing wrong with what you’ve written but here’s another approach using a record list:

property searchReplace : {{"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"}}

set column_letter to text returned of (display dialog "Enter the column letter" default answer "" buttons {"Enter"} default button 1)

tell application "Microsoft Excel"
	activate
	set the targetRange to (column_letter & ":" & column_letter)
	repeat with thisSearchReplace in searchReplace
		set searchVar to item 1 of thisSearchReplace
		set replaceVar to item 2 of thisSearchReplace
		replace range targetRange what searchVar replacement replaceVar
	end repeat
end tell

Hope it helps,
Nik