Check Phone Number List against website and return Phone Line Type

BGDunbar

Board Regular
Joined
Jul 26, 2016
Messages
79
I have a list of phone numbers that I want to determine if Cell, Landline, or VOIP. I have a website that will do this but will only check one at a time. The website is https://www.phonevalidator.com/index.aspx After entering a phone number it comes back to https://www.phonevalidator.com/results.aspx

I want to bounce an Excel list of 10K numbers in Column C “Phone” against this website and have it enter the Phone Line Type into Column E “Type”.

I am using Excel 365 on a Windows 10 Machine.

Can this be done with VBA?

Thank you,
Betty
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Betty,

You might consider the following...

Code:
Sub ScrapePhoneType_1023932()
''''Set a reference to Microsoft Internet Controls
Application.ScreenUpdating = False
Dim objIE As Object
Dim r As Range, rng As Range

Set rng = Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row)
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True

On Error GoTo errHandler:
For Each r In rng
    With objIE
        .navigate ("https://www.phonevalidator.com/index.aspx")
            Do
            DoEvents
            Loop Until objIE.ReadyState = 4
        .document.getElementById("ContentPlaceHolder1_txtPhone").Value = r.Value
        .document.getElementById("ContentPlaceHolder1_SearchButton").Click
        .navigate ("https://www.phonevalidator.com/results.aspx")
            Do
            DoEvents
            Loop Until objIE.ReadyState = 4
        Cells(r.Row, 5).Value = .document.getElementById("ContentPlaceHolder1_PhoneTypeLabel").innerhtml
    End With
Next r

errHandler:
    objIE.Quit
    AppActivate "MicroSoft Excel"
    Application.ScreenUpdating = True
    MsgBox "The dishes are done, dude!"
End Sub

The code utilizes Internet Explorer, so if not already available you'll need to install it.

Please set a reference to Microsoft Internet Controls in the Visual Basic Editor. (Tools / References)

An invalid phone number (or missing phone number) will have the same phone type as the previous entry.

My test data was quite small so I have no clue how it'll behave on a large data set.

Please promise you won't use this for robo calling. ;)

Cheers,

tonyyy
 
Upvote 0
Hi tonyyy... The code works for a few records and then seems to bomb. I get between 10 and 30 records before it just stops. I watched the VBA window and did not see any messages or anything there. It doesn't even tell me it's bombed. It just stops at the enter phone number page and sits there. No error msg or anything. If I close IE, then debug, it goes to the AppActivate "MicroSoft Excel" line.
 
Upvote 0
Hi Betty...

Looking a little more closely at the website, I found this paragraph:

"Automatic scraping or harvesting of data at this site is not permitted. Please note we do not allow this service or data to be re-purposed. If you require this type of processing, we recommend you investigate our batch processing options. We reserve the right to refuse service to any user at any time at our sole discretion."

And having seen that, I can't condone or support scraping from this site. Sorry.

As for the existing code, it does work on my small test sample, but the site might very well have some kind of monitor to prevent lengthy scrapes. You could type an apostrophe (') in front of the line "Application.ScreenUpdating = False" - this will allow excel to capture the results one at a time rather than as a batch at the end; so if the scrape "bombs" you could save off the results and start over where it left off.

It might be easier and quicker, though, to use the Phone Validator batch service. Send them your file with phone numbers and they'll return it completely annotated.

Good luck!
 
Last edited:
Upvote 0
Thank you for your help. I had not seen the batch option. At 1.5 cents each it's might be worth it to have them do it. I will check with my hubby and see what he says.

Again, thank you... :)
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top