Extract data from a site that does query with Java script

Question

I want to get data from an Internet site.

I get

run-time error '91'

I modified earlier codes.

Sub DENEME()
    Dim S As String
    Dim html As HTMLDocument
    Dim hTable As HTMLTable
    Dim clipboard As Object
    Set html = New HTMLDocument
    With New XMLHTTP60
        .Open "GET", "https://www.scorespro.com/basketball/results/date/2019-02-15", False
        .setRequestHeader "content-type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send
        S = .responseText
    End With
    html.body.innerHTML = S
    Set hTable = html.querySelector(".matches-data")
    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    clipboard.SetText hTable.outerHTML
    clipboard.PutInClipboard
    Range("A1").PasteSpecial
End Sub

@QHarr, if you take a hand. – Cumhur Ay Jun 02 '19 at 14:17 — Cumhur Ay, Jun 02 '19 at 14:17

score 1 · Answer 1 · answered Jun 02 '19 at 12:42

1

Your selector string is set up so that it looks for the first element whose class is matches-data. However, matches-data is the id. As a result, hTable is being assigned Nothing, hence the error. Instead, try the following...

Set hTable = html.querySelector("#matches-data")

answered Jun 02 '19 at 12:42

Domenic

7,844
2
9
17

It took the data. However, It did not take the data in the form of a table. – Cumhur Ay Jun 02 '19 at 14:08

QHarr · Accepted Answer · 2019-06-02T15:31:36.957

A few points to note.

Yes it is id not class for that element but that element is a div and copy pasting via clipboard the outer html will only paste the html of the div - not all tables within.
What you want is a list of tables so you need to change your selector to get the tables within that div
As using clipboard and there are merged output cells you need to find the last used row independent of column and add 1 to write out next table to a few row.
If unsure about your selector use the search bar in browser as shown [here].1

VBA:

Option Explicit
Public Sub Deneme()
    Dim s As String, ws As Worksheet, tables As Object, i As Long
    Dim html As HTMLDocument, clipboard As Object
    Set html = New HTMLDocument
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Application.ScreenUpdating = False
    With New XMLHTTP60
        .Open "GET", "https://www.scorespro.com/basketball/results/date/2019-02-15", False
        .setRequestHeader "content-type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send
        s = .responseText
    End With

    html.body.innerHTML = s
    Set tables = html.querySelectorAll("#matches-data table")
    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    For i = 0 To tables.Length - 1
        clipboard.SetText tables.item(i).outerHTML
        clipboard.PutInClipboard
        ws.Range("A" & GetLastRow(ws) + 1).PasteSpecial
    Next
    Application.ScreenUpdating = True
End Sub

Public Function GetLastRow(ByVal sh As Worksheet) As Long
    On Error Resume Next
    GetLastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

There are site moderators that remove comments deemed unnecessary so they may have removed. Also, comments can be removed by scripts that run as part of site content curation. — QHarr, Jun 03 '19 at 07:38
I think you're really super in the VBA. And I want this to be known. — Cumhur Ay, Jun 03 '19 at 07:41
That is kind. The way the site works is that appreciation for content is via voting. The idea is to curate quality content that serves future readers so anything other than that, however kind, can be removed. — QHarr, Jun 03 '19 at 07:43
I understand. Thank you for answering all my questions and showing your interest. — Cumhur Ay, Jun 03 '19 at 07:51

Extract data from a site that does query with Java script

2 Answers2