Common Application

[JohnL]

Short but large macro zip codes

 

I would like a macro when filling out a form that complete the city, state and zip based on entry of zip. Example short key prefix followed by US##### (USzipcode) or hot key producing dialog box requesting zip code. Then the macro would look up the city-state save a variable and complete text type of city <tab> state <tab> zip. I wrote one a couple years ago that went to an excel sheet that listed the zips that found zip city and state and transferred to the application. However it was slow and cumbersome since it required disk access and cross application and seem to work about 75% of the time. It would be nice if the zip data were contained within the macro so you don’t have to load the excel file but not clog the memory with the macro itself. Any help would be appreciated

 

Thanks

 

John

[randallc]

Hi, John,

 

I'd be interested to see how reliable my new Excel com macro function was in this sort of situation; I have no zip codes, not being n USA, but could you email me the excel sheet to try?

 

(prima facie, of course, it may work better with "text file process" anyway if you changed it to a text or csv file?)

 

Best, Randall

[randallc]

Hi again,

 

THIS MACRO GOES THE WRONG WAY: FINDS THE ZIP FROM FIRST FEW LETTERS OF THE TOWN NAME; is the State in that text file to do your request?

 

I looked into it ; I could only find November 2004 on the net.Large txt file, so you would have to put it n your ME Install directory as "txt" file.

 

Fast, I think (2 secs?

 

Only takes the first 34 possibilities unless you have code Wizard and my MEbasic (needs new version rather than those posted; let me know if you want them) installed (as limitation of multi choice menu).

 

Best Randall

USZIPCODESDATATEXT.zip

[randallc]

Here it is, correct way! [*** you stll need the ZIP data file in this thread, previous post**, as well!]

 

<1second, reliable I hope!

Randall

TownFromZIP.mxe

[Cory]

Don't forget that in many ZIPs there are more than one cities. In my case of 92028 I could be in Fallbrook or Rainbow. So don't start at the fist return.

 

If I were you I would create a CSV file of the ZIP codes and use the ASCII Process File command. This would avoid your problem with Excel, timing and everythign. Just write it so that if there is more than one return you prompt the user for the correct city. This would be super simple to write.

[kevin]

Insight Software Solutions (the makers of Macro Express) also sells a product named Zip Express. It has some rudimentary macro commands that will handle what you are trying to do. However, if you combine it with the macro commands in Macro Express you have the ultimate in control.

 

For more information about Zip Express visit www.getzips.com.

 

The Zip Code information is updated quarterly.

[randallc]

EDIT; posted twice "flood control"/ "Server Error"!

[randallc]

Hi, Cory,

If I were you I would create a CSV file of the ZIP codes and use the ASCII Process File command. This would avoid your problem with Excel, timing and everythign. Just write it so that if there is more than one return you prompt the user for the correct city. This would be super simple to write.

Hey, I thought I HAD already written it in the previous line in this thread!

I used the "Text File Process" as it seemed twice as fast (much more fiddly)!

[AND had included possibility of more than one result]

What do you think?

 

Randall

 

PS - Check the "Multiple Choice Box" in both "mxe" files above; had you beeen using the %CRLF% like this to build the Multiple choice menu?

Text File Begin Process: "USZIPCODESDATA.txt"

\\REM other lines

      Variable Set String %T99% "%T99%%T2%%CRLF%"

\\REM other lines

Text File End Process

    Multiple Choice Menu: ZIP code

 

<BTFBEG:002:000001:000000:%InstallPath%\USZIPCODESDATA.txt><TVAR2:99:01:%T99%%T2%%CRLF%><BTFEND><MENU2:1:T:98:CenterCenter:ZIP codeWhich one?%T99%>

[Lars_Sandberg]

Hi, I made such macro, working with danish zip-codes which consists of 4 digits, like:

6000 Kolding

 

I got a complete list of zip codes in an excel spreadsheet, which I converted to a tab separated ascii file, which my macro use.

So when I use the shortkey "pbx" after the zip code, my macro completes the city name like:

6000pbx

is made to:

6000 Kolding

 

Here is the code:

Keystroke Speed: 40 Milliseconds

Text Type: <ARROW LEFT><ARROW LEFT><ARROW LEFT><ARROW LEFT><SHIFTD><ARROW RIGHT><ARROW RIGHT><ARR...

// Postnr

Variable Set String %T1% from Clipboard

ASCII File Begin Process: "postnr-2005.txt" (Tab Delimited Text )

If Variable %T1% = variable %T2%

Break

End If

ASCII File End Process

Text Type: <ARROW RIGHT><SPACE>%T3%

 

<SPKEY:0040><TEXTTYPE:<ARROW LEFT><ARROW LEFT><ARROW LEFT><ARROW LEFT><SHIFTD><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><SHIFTU><CONTROL>c><REM2:Postnr><TVAR2:01:03:><ADFBEG:F11:002:000001:000000:c:\postnr-2005.txt><IFVAR2:4:01:1:T2><BREAK><ENDIF><ADFEND><TEXTTYPE:<ARROW RIGHT><SPACE>%T3%>

 

Good luck

regards Lars