Click button, scrape in loop

Question

I am trying to get the miles between 2 postcodes that are in 2 cells.

I wrote the code to open the webpage and input the 2 postcodes.

I can't get it to click the button and then take the miles and put it in the cells and loop through the cells until empty.

I have tried (0) up to (7), I think its the 6th button in the html. I have also tried different getelements.

'start a new subroutine called SearchBot

Sub SearchBot()

    'dimension (declare or set aside memory for) our variables
    Dim objIE As InternetExplorer 'special object variable representing the IE browser

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'initiating a new instance of Internet Explorer and asigning it to objIE
    Set objIE = New InternetExplorer

    'make IE browser visible (False would allow IE to run in the background)
    objIE.Visible = True

    'navigate IE to this web page (a pretty neat search engine really)
    objIE.navigate "http://www.ukpostcode.net/distance-between-uk-postcodes"

    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'in the search box put cell value
    objIE.document.getElementById("pointa").Value = _
      Sheets("Sheet1").Range("B2").Value

    'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'in the search box put cell "A2" value, the word "in" and cell "C1" value
    objIE.document.getElementById("pointb").Value = _
      Sheets("Sheet1").Range("D2").Value

    'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    'code below doesnt    work''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'click the 'go' button
   objIE.document.getElementsByTagName("button")(6).Click

    'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'take miles and put in cell

    'add distance to sheet
    Range("e2").Value = getElementsByid("distance")

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    'close the browser
    objIE.Quit

    'exit our SearchBot subroutine
End Sub

I want the miles put in a cell next to the 2 post codes 2 cells and move to the next and do the same until the cells are empty.

QHarr · Accepted Answer · 2019-04-10 10:29:28Z

1

With a little javascript manipulation you can easily do this. The distance by road I think requires direction service which requires an API key. I am guessing this webpage was from the days before Google updated the geo APIs to require paid for API keys.

I overwrite the window alert message and use javascript to read the value of the distance.

Option Explicit

Public Sub SearchBot()
    Dim objIE As InternetExplorer, ws As Worksheet, lastRow As Long, i As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set objIE = New InternetExplorer
    lastRow = ws.Cells(ws.rows.Count, "B").End(xlUp).Row 'Down to first blank. Assumes header in row 1
    Dim postcodes()
    postcodes = ws.Range("B2:D" & lastRow).Value

    With objIE
        .Visible = True
        .Navigate2 "http://www.ukpostcode.net/distance-between-uk-postcodes"

        Do While .Busy = True Or .readyState <> 4: DoEvents: Loop

        .document.parentWindow.execScript "window.alert = function() {};"

        For i = LBound(postcodes, 1) To UBound(postcodes, 1)

            .document.getElementById("pointa").Value = _
                                                          postcodes(i, 1)

            .document.getElementById("pointb").Value = _
                                                         postcodes(i, 3)

            .document.querySelector("[value='Calculate Distance']").Click

            Application.Wait Now + TimeSerial(0, 0, 1)

            .document.parentWindow.execScript "document.title = document.getElementById('distance').value;"

            ws.Cells(i + 1, "E") = .document.Title
        Next
        objIE.Quit
    End With
End Sub

edited Apr 10, 2019 at 10:29

answered Apr 10, 2019 at 10:14

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gary Arg Over a year ago

hey again Qharr, you are on here a lot bless you, always helping others out. I am about to try this one. no doubt you are have done it. big thumbs up.

QHarr Over a year ago

Any problems let me know. I'm also happy to go into more detail about what each step is doing.

Gary Arg Over a year ago

QHarr, that worked thank you, it also showed me how to loop through cells as well. I really appreciate it.

Collectives™ on Stack Overflow

Click button, scrape in loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related