0

I am trying to extract data from HTML tables like the one at the site in the code below and am not sure what I'm doing wrong as I am not familiar with HTML classes, elements, etc. I found some code that reportedly worked, however I did not have access to visit the site provided so could not fully test. Code I am trying to run is below, any help would be much appreciated.

Option Explicit

Sub test()

Dim oDom As Object: Set oDom = CreateObject("htmlFile")
Dim x As Long, y As Long
Dim oRow As Object, oCell As Object
Dim data

y = 1: x = 1

With CreateObject("msxml2.xmlhttp")
    .Open "GET", "https://www.nascar.com/results/racecenter/2021/nascar-cup-series/cook-out-southern-500", False
    .send
    oDom.body.innerHTML = .responseText
End With

With oDom.getElementsByTagName("race-center-entry-list race-center-lineup")(0)
    ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
    For Each oRow In .Rows
        For Each oCell In oRow.Cells
            data(x, y) = oCell.innerText
            y = y + 1
        Next oCell
        y = 1
        x = x + 1
    Next oRow
End With
Sheets(1).Cells(1, 1).Resize(UBound(data), UBound(data, 2)).Value = data
End Sub
7
  • "race-center-entry-list race-center-lineup" is not a valid HTML tag name Commented Sep 3, 2021 at 0:06
  • ... maybe try getElementsByClassName instead Commented Sep 3, 2021 at 0:34
  • Thanks Tim. I've abandoned this method and moved on to a simple copy-paste using IE. When I perform the 'select all' and 'copy' commands manually (right click the open web page) I can manually use paste special to get the data to Sheet1 in HTML, Unicode Text, or Text formats. However when I automate using the code I get several blank, selected cells at the top of Sheet1. Any ideas? Commented Sep 3, 2021 at 14:18
  • 'Sub Test() Dim IE As Object Sheets("Sheet1").Range("A1:A1000").ClearContents Set IE = CreateObject("InternetExplorer.Application") With IE .Visible = True .navigate "nascar.com/standings/nascar-cup-series" Do Until .readyState = 4: DoEvents: Loop End With IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT ActiveSheet.PasteSpecial Format:="Unicode Text", link:=False, DisplayAsIcon:=False IE.Quit End Sub' Commented Sep 3, 2021 at 14:20
  • you can get all the data from this endpoint https://cf.nascar.com/cacher/2021/1/5078/weekend-feed.json - you will need a json parser to handle the response. The data you want is dynamically retrieved by the webpage and not present in the response you are currently getting. Commented Sep 3, 2021 at 19:02

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.