Excel VBA IE verses XMLHTTP discrepencies

Question

I am scraping the following web site https://www2.asx.com.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 = "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

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

score 0 · Answer 1 · answered Nov 02 '20 at 07:20

0

Your question has nothing to do with Excel.

Only Internet Explorer supports all user based internet interactions.

XMLHTML supports only what is required to get data from friendly web servers. It is not meant for browsing.

answered Nov 02 '20 at 07:20

user14562295

1

score 0 · Answer 2 · answered Nov 02 '20 at 12:54

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

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

Thanks Zwenn, I did look for xhr links, but missed this. – Flaxman Nov 03 '20 at 04:06 — Flaxman, Nov 03 '20 at 04:06

Excel VBA IE verses XMLHTTP discrepencies

2 Answers2

Linked