Macro to download house prices from web
 

Hi,

I am very new to VBA and wanting to write a code to automate the download of housing data for suburbs, vacancy rates etc from various websites.

So far I have the following code, but already I know this is very inefficient and cannot work out the best way to extract the relevant data.


Private Sub btnGetData_Click()

Dim IE As New InternetExplorer
IE.navigate "http://whatpostcode.com.au/postcodes/" & Range("State").Value & "/" & Range("suburb").Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

Dim Doc As HTMLDocument
Set Doc = IE.document
Dim sh3 As String
sh3 = Trim(Doc.getElementsByTagName("h3")(0).innerText)
Range("Postcode").Value = sh3
IE.Quit

Dim IE2 As New InternetExplorer
IE2.Visible = True
IE2.navigate "http://www.rs.realestate.com.au/cgi-bin/rsearch?a=sp&s=" & Range("state").Value & "&u=" & Range("suburb").Value
Do
DoEvents
Loop Until IE2.readyState = READYSTATE_COMPLETE
Dim Doc2 As HTMLDocument
Set Doc2 = IE2.document
Dim sth As String
sth = Trim(Doc2.getElementsByTagName("th")(5).innerText)
IE2.Quit

End Sub


Basically user inputs a suburb name and state on the worksheet. The code then uses these and finds the postcode from the first website.

Then code navigates to a second website and I want it to extract the median house price data from tables on this website. Ideally, I would like the code to set a new worksheet for a new suburb search and save this data in a list. If the user searches the same suburb, any new data will be appended to the end of the table to get a long history.

My issue comes from extracting the data from the table. How to automate this in as few lines of code as possible, and how to make VBA recognise if this is a new suburb search or existing sheet, and how to append new data to end of list.