Fetch specific table only from website into Excel

Question

I need to fetch the table from http://www.zillow.com/homes/comps/67083361_zpid/ into Excel using VBA. I just want the table, nothing else. But when I'm using:

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
    .Visible = True
    .Navigate "http://www.zillow.com/homes/comps/67083361_zpid/"
    Do While .ReadyState <> 4: DoEvents: Loop
    Debug.Print .document.Body.outerText
End With

it gives me text like:

4723 N 63rd Dr$63,50008/17/201241.752,0747,6751972$360.11

for each product which I can't analyze and store into different cells of Excel.

So is there a way I can fetch the page data in a manageable way. I am OK if I need to traverse a loop for this. Also I can do additional processing to fill the row data into Excel properly.

score 11 · Accepted Answer · answered Oct 22 '12 at 08:36

11

I'd use the below since I find query tables slow and IE excruciatingly slow ;)

Sub GetData()
    Dim x As Long, y As Long
    Dim htm As Object

    Set htm = CreateObject("htmlFile")

    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://www.zillow.com/homes/comps/67083361_zpid/", False
        .send
        htm.body.innerhtml = .responsetext
    End With

    With htm.getelementbyid("comps-results")
        For x = 0 To .Rows.Length - 1
            For y = 0 To .Rows(x).Cells.Length - 1
                Sheets(1).Cells(x + 1, y + 1).Value = .Rows(x).Cells(y).innertext
            Next y
        Next x
    End With

End Sub

answered Oct 22 '12 at 08:36

SWa

4,343
23
40

Hi, Can you please look into my question at [stackoverflow-question](http://stackoverflow.com/questions/13932393/extract-data-from-a-web-page-using-vba) I am sure you would have the best answer! :) – TechGeek Dec 18 '12 at 11:55
I opened up a blank module and the code stops with `object variable or with block not set` on `For x = 0 To .Rows.Length - 1` – phillipsK Feb 04 '15 at 23:53
What library does the htmlFile object come from? – robartsd Feb 12 '15 at 17:37
@robartsd The Microsoft HTML Object Library – SWa Sep 16 '15 at 09:20

score 5 · Answer 2 · answered Oct 20 '12 at 15:45

I have done it using following code:

Sub FetchData()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.zillow.com/homes/comps/67083361_zpid", Destination:=Range( _
        "$A$1"))
        .Name = "67083361_zpid"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Fetch specific table only from website into Excel

2 Answers2

Linked