Excel VBA IE克服了XMLHTTP的差异 [英] Excel VBA IE verses XMLHTTP discrepencies

问题描述

我正在抓取以下网站检索澳大利亚股票市场的指数列表.

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 = "https://www2.asx.com.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 = "https://www2.asx.com.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:
https://www.asx.com.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:
https://www.asx.com.au/asx/1/index-info?callback=processASXIndices

Sub GetXmlAsx()
Dim XMLRequest As New MSXML2.XMLHTTP60
Dim url As String

  url = "https://www.asx.com.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模块:
https://github.com/VBA-tools/VBA-JSON

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

这篇关于Excel VBA IE克服了XMLHTTP的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！