vba 仅从网站获取特定表格到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12989687/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fetch specific table only from website into Excel
提问by Tejas
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:
我需要使用 VBA从http://www.zillow.com/homes/comps/67083361_zpid/获取表格到 Excel 中。我只想要桌子，别无他物。但是当我使用时：
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
4723 N 第 63 博士 $63,50008/17/201241.752,0747,6751972$360.11
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.
那么有没有一种方法可以以可管理的方式获取页面数据。如果我需要为此遍历一个循环，我就可以了。我也可以做额外的处理来正确地将行数据填充到 Excel 中。
回答by SWa
I'd use the below since I find query tables slow and IE excruciatingly slow ;)
我会使用下面的，因为我发现查询表很慢而且 IE 非常慢;)
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
回答by Tejas
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"))
.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
