How to get rid of the loop (used within my scraper) when a value is none?

[shahin]

I've written a macro which is able to parse phone numbers against different company names using google maps. The scraper takes input from spreadsheet then performs a search in the google maps searchbox and when it finds any lead then it scrapes that phone number and place it next to it's search in the spreadsheet. I didn't use any hardcoded delay that is why it works a lot faster than I imagined. However, the only problem is that when it doesn't find any match the loop continues on and on. How to fix this error so that if it fails to find a lead it will go for the next one and so on?

I've used five company names from Range("A1") to Range("A5") which are valid searches. When the scraper is made to run then you can see that the next cell of each range has been populated with accurate phone numbers.

I've used another three invalid searches from Range("A7") to Range("A9") [currently they are not used in the scraper]. When they are included in my scraper then the google maps can't find them and the loop continues on and on. How to fix this?

Valid searches are:

ACCO Engineered Systems Inc.
Agron, Inc.
Albert C. Kobayashi

invalid searches are:

Acadianne Ambulance
Essentiallet Ingredients
Bimbano Manuf

This is the script:

Sub Get_Phone_Number()
    Dim IE As New InternetExplorer, HTML As HTMLDocument
    Dim post As Object, cel As Range

    For Each cel In Range("A1:A5")
        With IE
            .Visible = True
            .navigate "https://www.google.com.bd/maps/@23.8164102,90.360714,15z?hl=en"
            While .Busy = True Or .readyState < 4: DoEvents: Wend
            Set HTML = .document
        End With
   
        HTML.getElementById("searchboxinput").Value = cel.Value
        HTML.getElementById("searchbox-searchbutton").Click
       ''I've used the below loop to avoid using hardcoded delay
        Do
            Set post = HTML.querySelector("[data-section-id='pn0'] .widget-pane-link[jsan='7.widget-pane-link']")
            DoEvents
        Loop While post Is Nothing
   
        cel(1, 2) = post.innerText
    Next cel
    IE.Quit
End Sub

Btw, the loop continues on because I created it in such a way so that I can avoid any hardcoded delay. I'm attaching a spreadsheet with the macro for your kind consideration.

 

[Chihiro]

Simple answer is no. Unless you delve into jQuery and Ajax (using ScriptControl), there's no event that you can detect from VBA.

Just set limit on number of loops you perform (you'd probably need hard coded wait time inside loop).

 

[shahin]

I can get around that with the below code. However, I wished to keep the "do loop" within my scraper to avoid using hardcoded delay.

This can handle wrong values:

Sub Get_Phone_Number()
    Dim IE As New InternetExplorer, HTML As HTMLDocument
    Dim post As Object, cel As Range
   
    For Each cel In Range("A1:A8")
        With IE
            .Visible = True
            .navigate "https://www.google.com.bd/maps/@23.8164102,90.360714,15z?hl=en"
            While .Busy = True Or .readyState < 4: DoEvents: Wend
            Set HTML = .document
        End With
       
        HTML.getElementById("searchboxinput").Value = cel.Value
        HTML.getElementById("searchbox-searchbutton").Click
       
        Application.Wait Now + TimeValue("00:00:05")
       
        Set post = HTML.querySelector("[data-section-id='pn0'] .widget-pane-link[jsan='7.widget-pane-link']")
        If Not post Is Nothing Then
            cel(1, 2) = post.innerText
        End If
    Next cel
    IE.Quit
End Sub

 

[stefanoste78]

good morning
I am trying this shinhin macro. It does not work anymore.
There is someone who can update it.

Also could you tell me which parts of the code need to be modified in order to insert other types of extractions (address and geographic coordinates?