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