Help on getting real time data value from an HTML table in VBA

[Mauriz73]

Dear all,
I get in touch with this forum hoping that someone can help me.
I would need a vba code that can grab live data from this website: LiveTiming Demo

As you can see data are stored in a table and also the refresh rate is high due to the application.
What I would start from is having the same table refreshing in excel and then from there I would develop other parts of code manipulating those data (I already know how to do them) but I have no experience in working with html pages with VBA.

Is there anyone who would help me on this?

 

[Mauriz73]

Up
Anyone interested in this kind of programming?

 

[Mauriz73]

At the moment I'm at this point.
Is there anyone interested in helping going throught the process of making it working live?

Option Explicit

Const sSiteName = "https://livetiming.getraceresults.com/demo#screen-results"

Private Sub getHTMLContents()
    ' Create Internet Explorer object.
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False          ' Keep this hidden.
    
    IE.navigate sSiteName
    
    ' Wait till IE is fully loaded.
    While IE.readyState <> 4
        DoEvents
    Wend
    
    Dim oHDoc As HTMLDocument     ' Create document object.
    Set oHDoc = IE.document
    Dim oHTable As HTMLTable
    
    Dim Table As Object
    Set Table = oHDoc.getElementsByClassName("resultsGrid")
    
    Dim btn As Object
    Dim tRows As Object
    Dim temp As Object
    Dim tHead As Object
    Dim tCells As Object
    Dim np As Variant
    Dim numPages As String
    Dim url As String
    Dim pos As Integer
    Dim rNum As Integer
    Dim cNum As Integer
    Dim h As Object
    
    
    Set tRows = Table(0).getElementsByTagName("tr")
        
     ' Create variables to track the row and column to output the
     ' text on our spreadsheet
     rNum = 3
     cNum = 1

     ' First we can get the column headings which use the "th" tag
     Set tHead = Table(0).getElementsByTagName("th")
    
    Dim a As String
    Dim r, c As Object
    
    
     ' Loop through each column heading
     For Each h In tHead
          ' Output the contents of the cell to the spreadsheet
          a = h.innerText
          Worksheets(1).Cells(rNum, cNum).Value = a
          ' Increase the cNum value so the next time around the
          'data will output to the column to the right
          cNum = cNum + 1
     Next
        
     ' Move on to the next row before pulling the data and reset
     ' the column back to 1
     rNum = rNum + 1
     cNum = 1
        
     ' Loop through each row in the table
     For Each r In tRows
            
          ' Within each row, pull each cell by using the
          ' getelementsbytagname method and use the table tag "td"
          Set tCells = r.getElementsByTagName("td")
            
            ' Loop through each cell of the row
            For Each c In tCells
                ' Output the contents of the cell to the
                ' spreadsheet
                Worksheets(1).Cells(rNum, cNum).Value = c.innerText
                ' Increase the cNum value so the next time around
                ' the data will output to the column to the right
                cNum = cNum + 1
            Next
            
            ' When we switch to the next row of the table,
            ' increase the rNum value so we go to the next
            ' row of our spreadsheet, and also reset back to
            ' column number 1
            rNum = rNum + 1
            cNum = 1
     Next

    IE.Quit
    Set IE = Nothing
    Set oHTable = Nothing
    Set oHDoc = Nothing
End Sub