PDA

View Full Version : get Zip codes from web based on address in cell excel VBA



raj249
05-28-2014, 08:49 AM
Hi all,

New here and total noob in exce.

I was looking for a code to get Zip Codes based on address in excel sheet.

I found exactly what I was looking here itself but its a UDF.


Public Function getZipCode(phoneNum As String) As String
'Retrieves Zip code for a Given Phone number
'From
Dim xmlhttp As Object
Dim strURL As String

Set xmlhttp = CreateObject("msxml2.xmlhttp")
phoneNum = Trim(Replace(phoneNum, "-", ""))
strURL = "com/lookups/phonelocation.asp?number=" & phoneNum
With xmlhttp
.Open "get", strURL, False
.send
getZipCode = parseZip(.responsetext)
End With

Set xmlhttp = Nothing

End Function

Public Function parseZip(x As String) As String
'Parses outthe Zip code from a Phone number search on
?number=
Dim srch As String
srch = "ZipCityPhone.asp?"

If InStr(1, x, srch) <> 0 Then
x = Mid(x, InStr(1, x, srch) + Len(srch))
x = Mid(x, 1, InStr(1, x, ">") - 2)
parseZip = Trim(x)
Else
parseZip = "Not Found"
End If

End Function



What i need is a Macro.

Will any one please modify this in macro?

Regards

OG Loc
05-28-2014, 09:21 AM
To make it a macro, call the first one 'Sub' not function (and remove the argument and 'as string' from that line) and add a new line to make the output information go into your spreadsheet where you need it, e.g. replace the 'getZipCode = parseZip(.responsetext)' with 'Range("A1") = parseZip(.responsetext)'.

Then for that input phone number, you'll have to take that from somewhere in the sheet too, so replace 'phoneNum = Trim(Replace(phoneNum, "-", ""))' with 'phoneNum = Range("A2")'.

Replace A1 and A2 references with what you actually need based on your spreadsheet.

westconn1
05-28-2014, 02:30 PM
a UDF is just a function, it can also be called from a sub (macro)


sub getaddress
zip = getZipCode("0984545444")
msgbox zip
end sub