accessing html elements of ASX website dividend table using excel vba

1.1k views Asked by At

I am trying to download divident data from ASX!

I need to know how to loop through each row in the table and each td (data element) in that table to print the innertext to the excel worksheet.

Here is my code.... using this as a guide

VBA excel For each row in table match cell in spreadsheet with cell in webpage table

Sub WebTable()

Dim ie As New InternetExplorer
Set ie = Nothing
ie.Visible = False
Dim url As String
Dim doc As HTMLDocument
Dim items As Variant
Dim tr As String
Dim tbody As String
Dim td As String
Dim r As Double
Dim i As Double

Dim tbObj As Variant
Dim trObj As Variant
Dim tdObj As Variant
Dim tdItem As Variant
Dim element

    tbody = "tbody"
    tr = "tr"
    td = "td"
    url = "http://www.asx.com.au/asx/markets/dividends.do?by=asxCodes&asxCodes=ont&view=latest"

    ie.navigate url
    Debug.Print url

    'loop until complete so program doesn't freeze
    Do
    DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

'get everything in the dividends table using its id
    Set tbObj = ie.document.getElementById("dividends") 'all table elements including headers

    'get the four rows for the ONT stock (headings and data elements
    Set trObj = tbObj.getElementsByTagName(tr)


    'get the eight data items for each row of the ONT stock e.g stock price date etc.
    For Each trObj In tbObj

'print each data element one by one for each of the four rows
        Set tdObj = tbObj.items(0).getelementbytagname("td")
        Set Count = tdObj.length
        Debug.Print tdObj(0).innerText
    Next trObj

End Sub
1

There are 1 answers

26
Santosh On BEST ANSWER

Try this

Sub WebTable()

    Dim ie As New InternetExplorer
    Dim url As String

    Dim trCollection As Object
    Dim tdCollection As Object

    Dim tdObj As Object
    Dim trObj As Object


    url = "http://www.asx.com.au/asx/markets/dividends.do?by=asxCodes&asxCodes=ont&view=latest"

    ie.navigate url
    Debug.Print url


'loop until complete so program doesn't freeze
    Do
        DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

'get everything in the dividends table using its id
    Set tbObj = ie.document.getElementById("dividends") 'all table elements including headers

'get the four rows for the ONT stock (headings and data elements
    Set trCollection = tbObj.getElementsByTagName("tr")

Dim i As Integer
 i = 1
'get the eight data items for each row of the ONT stock e.g stock price date etc.
    For Each trObj In trCollection

    If i = 1 Then
'print each data element one by one for each of the four rows
        Set tdCollection = trObj.getElementsByTagName("th")
        Else
          Set tdCollection = trObj.getElementsByTagName("td")

      End If

      i = i + 1

        For Each tdObj In tdCollection

            Debug.Print tdObj.innerText
        Next

    Next

End Sub

enter image description here