Solved: Get data(Zipcodes) from web page and paste to excel

[vishwakarma]

Hi,

I need to extract zipcodes(primary) from a webpage on the basis of phone numbers and paste it into the excel sheet in the next cell front of the corrsponding phone numbers.

I have a list of Phone numbers and wanted to extract zipcodes on the basis of these.



Thanks,

[vishwakarma]

Can anyone help me on this?

Thank You...

[Aussiebear]

We have some members who are fairly good in working with webpages..... maybe they haven't been on line yet. Please be patient.

[Shred Dude]

What is the source of the zipcode information? I didn't see a web page referenced in your workbook.

[gcomyn]

I don't see any code in the workbook at all....

GComyn

[vishwakarma]

I'm new to vb and have very limited knowledge about it. I've been searching codes for this but no luck, I thought you guys might help me in this...

The website from where I want to export the data is :- melissadata.com/lookups/phonelocation.asp?number

[vishwakarma]

Attached is the data sheet with a website link... THANKS

[khushii]

I need to extract zipcodes(primary) from a webpage on the basis of phone numbers and paste it into the excel sheet in the next cell front of the corrsponding phone numbers.

Vishwakarma,

Here is my effort.

The Get ZipCodes button gets the primary Zip Code from the web page u specified..and paste it in the Column next 2 phone numbers.
there r some Numbers which doesnt have Primary Zipcodes..for that it diaplays #NA.


I have newly joined the forum, still in process of learning VBA.


hope it helps..

[vishwakarma]

This is Awesome.... Thank you..Thank you...Thank you a lot...

This is the best forum I've joined till date...

[Shred Dude]

You might find this to be faster...

Public Function getZipCode(phoneNum As String) As String
'Retrieves Zip code for a Given Phone number
'From http://www.melissadata.com/lookups/p...ber=XXXXXXXXXX

Dim xmlhttp As Object
Dim strURL As String

Set xmlhttp = CreateObject("msxml2.xmlhttp")
phoneNum = Trim(Replace(phoneNum, "-", ""))
strURL = "http://www.melissadata.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
'http://www.melissadata.com/lookups/phonelocation.asp?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

The attached workbook uses the UDF to retrieve the data. Copy down to as many rows as you like.

Or, alternatively, incorporate the function into a looping routine that will grab numbers and retrieve the zip code when you desire.

Either way, I think you'll see a significant performance improvement as compared to the webconnect query table approach, especailly if you plan on doing any volume of data.

Good luck with it.

Shred Dude

[vishwakarma]

This is just great... you guys just Rock... THANKS A LOT...

I've been doing R&D on this for a almost 2 months on this and in-fact put this query on other forum as well but never got the solution...This is just amazing....Thanks

[dmyro]

I ran the above solution from Shred Dude and it worked like a charm. Is there any way the function can be created to search with an Address, City , State field entered instead of with a phone number? I was trying to do this through a web query but think your method to be faster as I have large volumes to assign. I was thinking the USPS website (that I can't post yet) might provide the web portal for the build. The above fields are all required to generate the zip. Any help or better focus on the problem would be much appreciated.

[grumpygirl]

Hi all! I am still learning a lot about coding and VBA. I am working for a small business. I am trying to do something similar to what was originally posted here. I am looking to create a list of addresses based off of zipcodes. I am also using the same website. My site is melissadata.com/lookups/zipnumber.asp Ideally what I will do with this list is create labels for snail mail post cards. IF anyone could help me out and point me in the right direction or whatever that would be great. I already have a code that will put my list into label format. I just need help in generating my list. Any help would be greatly appreciated. Thank you in advance! - gg

[poielsd]

Can anyone help me on this?

[poielsd]

I believe you may find many online tools that would transfer the telephone into a postal code. All telephones have area codes that are linked to a zip code of a certain territory. This information is unique throughout the whole country. I am in https://worldpostalcode.com/united-s...lorida/orlando. So, to identify the zip codes and area codes of this location, you need just to make a proper request on the Internet.

[Aussiebear]

Can anyone help me on this?

What help do you need?

[p45cal]

What help do you need?

Can't we just lose this 'new' member. All posts are cut'n'paste of other replies. Nonsense.