Fetch Data from HTML Website using VBA - FREEMAPTOOLS.COM

Question

I am trying to input a post code into this website and pull the results into Excel using VBA

http://www.freemaptools.com/find-uk-postcodes-inside-radius.htm

In short you input a post code and set a radius either in miles or KM and it gives you all the post codes within that area. As you can imagine this tool would be very useful!

This is what I have so far:

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = 0

url = "http://www.freemaptools.com/find-uk-postcodes-inside-radius.htm" 
ie.Navigate url


state = 0
Do Until state = 4
DoEvents
state = ie.readyState
Loop

It would be good if say cell A1 had the post code and cell A2 had the distance in KM. This script would then look at this as the variable.

I am not 100% sure put I think I then need to Parse the result to put them each into there own cell.

Any help with this would be incredible!

Santosh · Accepted Answer · 2014-01-10 01:27:17Z

1

Here you go

Download the file

 Sub postcode()

    Dim URL As String, str_output As String, arr_output() As String, row As Long
    Dim obj_Radius As Object, obj_Miles As Object, post_code As Object
    Dim btn As Object, btn_Radius As Object, tb_output As Object
    URL = "http://www.freemaptools.com/find-uk-postcodes-inside-radius.htm"

    Dim IE As Object
    Set IE = CreateObject("internetexplorer.application")

    IE.Visible = True
    IE.navigate URL

    Do While IE.readystate <> 4
        DoEvents
    Loop

    delay 5

    Set obj_Radius = IE.document.getelementbyid("tb_radius")
    obj_Radius.Value = ThisWorkbook.Sheets(1).Range("B1")


    Set obj_Miles = IE.document.getelementbyid("tb_radius_miles")
    obj_Miles.Value = ThisWorkbook.Sheets(1).Range("B2")

    Set post_code = IE.document.getelementbyid("goto")
    post_code.Value = ThisWorkbook.Sheets(1).Range("B3")

    Set btn_Radius = IE.document.getelementsbytagname("Input")
    For Each btn In btn_Radius
        If btn.Value = "Draw Radius" Then
            btn.Click
        End If
    Next

    Do While IE.readystate <> 4
        DoEvents
    Loop

    delay 10

    Set tb_output = IE.document.getelementbyid("tb_output")
    str_output = tb_output.innerText
    arr_output = Split(str_output, ",")

    row = 1
    For i = LBound(arr_output) To UBound(arr_output)
        ThisWorkbook.Sheets(1).Range("C" & row) = arr_output(i)
        row = row + 1
    Next

End Sub

Private Sub delay(seconds As Long)
    Dim endTime As Date
    endTime = DateAdd("s", seconds, Now())
    Do While Now() < endTime
        DoEvents
    Loop
End Sub

enter image description here

edited Jan 10, 2014 at 1:27

answered Jan 8, 2014 at 18:58

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

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

4 Comments

user3173870 Over a year ago

Thanks for your help with this. I ran this code and it displays the results in a pop up within Excel. Is it possible to put each result in a cell within Excel? So for each post code AL1, AL2, AL3, WL12 etc is displayed in cell C1, C2, C3 etc. Also with the code it looks as thought you have to manually input the post code into VBA can it look at a cell within Excel for example if cell A1 contains a post code and cell A2 contains a Distance (radius). Thank you for your help! Much appreciated.

user3173870 Over a year ago

This is incredible, what a great tool! Many people WILL find this helpful. Thanks again

user3173870 Over a year ago

I am trying to accept it but I need more reputation. I will do as soon as possible. Thanks again

user3173870 Over a year ago

Thank you for your help with this. I need some more code written which I want to pay you to make and am sure it will take you 10 minutes. Can you send me an e-mail please. [email protected]

Collectives™ on Stack Overflow

Fetch Data from HTML Website using VBA - FREEMAPTOOLS.COM

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related