仅从网站到Excel获取特定表格 [英] Fetch specific table only from website into Excel

问题描述

我需要从 http://www.zillow.com/homes/获取表格comps / 67083361_zpid / 使用VBA进入Excel。我只是想要桌子，没有别的。但是当我使用时：

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

它给我的文字如下：

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

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

对于我无法分析和存储到Excel的不同单元格中的每个产品。

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

所以有一种方法可以以可管理的方式获取页面数据。我可以，如果我需要遍历一个循环为此。另外我可以进行额外的处理，将行数据填入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.

推荐答案

表缓慢，IE令人难以置信地缓慢;）

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

这篇关于仅从网站到Excel获取特定表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！