get Zip codes from web based on address in cell excel VBA

[raj249]

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]

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]

a UDF is just a function, it can also be called from a sub (macro)

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