Excel VBA IE克服了XMLHTTP的差异

本文介绍了Excel VBA IE克服了XMLHTTP的差异的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在抓取以下网站 www2.asx.au/markets/trade-our-cash-market/overview/indices/real-time-indices

检索澳大利亚股票市场的指数列表.

which retrieve a list of indices on the Australia stock market.

我正在使用以下代码，该代码可以正常工作并返回标头和表数据.

I'm using the following code which works and returns both the header and the table data.

Sub GetIEAsx() Dim IE As New SHDocVw.InternetExplorer Dim HTMLDoc As MSHTML.HTMLDocument Dim HTMLDiv As MSHTML.IHTMLElement Dim HTMLTable As MSHTML.IHTMLElement url = "www2.asx.au/markets/trade-our-cash-market/overview/indices/real-time-indices" IE.Navigate url ' Wait while IE loading... Do While IE.Busy And Not IE.ReadyState = 4 DoEvents Application.Wait DateAdd("s", 1, Now) Loop Set HTMLDoc = IE.document Set HTMLDiv = HTMLDoc.getElementById("realTimeIndicesWidget") Set HTMLTable = HTMLDiv.getElementsByTagName("table")(0) WriteTableToWorksheet HTMLTable End Sub Public Sub WriteTableToWorksheet(TableToProcess As MSHTML.IHTMLElement) Dim TableSection As MSHTML.IHTMLElement Dim TableRow As MSHTML.IHTMLElement Dim TableCell As MSHTML.IHTMLElement Dim td As MSHTML.IHTMLElement Dim rowNum As Long Dim colNum As Long Dim OutPutSheet As Worksheet rowNum = 0 colNum = 0 Set OutPutSheet = ThisWorkbook.Worksheets.Add ' searh table section for results For Each TableSection In TableToProcess.Children For Each TableRow In TableSection.Children rowNum = rowNum + 1 For Each TableCell In TableRow.Children colNum = colNum + 1 OutPutSheet.Cells(rowNum, colNum) = TableCell.innerText Next TableCell colNum = 0 Next TableRow Next TableSection End Sub

但是当我使用XMLHTTP刮站点时，我得到的是header(thead)数据，而不是表(tbody)数据.任何帮助将不胜感激.

But when I use XMLHTTP to scrape the site I get the header(thead) data but not the table (tbody )data. Any help would be very much appreciated.

Sub GetXmlAsx() Dim XMLRequest As New MSXML2.XMLHTTP60 Dim HTMLDoc As New MSHTML.HTMLDocument Dim HTMLDiv As MSHTML.IHTMLElement Dim HTMLTable As MSHTML.IHTMLElement url = "www2.asx.au/markets/trade-our-cash-market/overview/indices/real-time-indices" With XMLRequest .Open "GET", url, False .send End With If XMLRequest.Status <> 200 Then MsgBox XMLRequest.Status & " - " & XMLRequest.statusText Exit Sub End If HTMLDoc.body.innerHTML = XMLRequest.responseText Set HTMLDiv = HTMLDoc.getElementById("realTimeIndicesWidget") Set HTMLTable = HTMLDiv.getElementsByTagName("table")(0) WriteTableToWorksheet HTMLTable End Sub

推荐答案

不会通过在xhr上加载html来加载 tbody 中的值.但是jou可以使用xhr从此链接中获取带有值的JSON: www.asx.au/asx/1/index-info?callback = processASXIndices

The values in tbody will not be load by loading the html over xhr. But jou can get the JSON with the values from this link with xhr: www.asx.au/asx/1/index-info?callback=processASXIndices

Sub GetXmlAsx() Dim XMLRequest As New MSXML2.XMLHTTP60 Dim url As String url = "www.asx.au/asx/1/index-info?callback=processASXIndices" With XMLRequest .Open "GET", url, False .send End With If XMLRequest.Status <> 200 Then MsgBox XMLRequest.Status & " - " & XMLRequest.statusText Exit Sub End If MsgBox XMLRequest.responseText End Sub

要处理JSON，您可以使用GitHub上的Tim Hall提供的VBA模块: github/VBA-tools/VBA-JSON

To process the JSON, you can use this VBA module provided by Tim Hall on GitHub: github/VBA-tools/VBA-JSON