How to scrape through VBA

[Wheatly]

I’m trying to scrape the following webpage https://crownbet.com.au/sports/soccer
And yet it loads https://crownbet.com.au and does not copy paste anything into Excel

Sub Macro1()

    Dim ie As Object
       Dim i As Long
       Dim strText As String
       Dim doc As Object
       Dim hTable As Object
       Dim hBody As Object
       Dim hTR As Object
       Dim hTD As Object
       Dim tb As Object
       Dim bb As Object
       Dim tr As Object
       Dim td As Object

       Dim y As Long, z As Long, wb As Excel.Workbook, ws As Excel.Worksheet

         Set wb = Excel.ActiveWorkbook
         Set ws = wb.ActiveSheet

         Set ie = CreateObject("InternetExplorer.Application")
         ie.Visible = True

         y = 1   'Column A in Excel
         z = 1   'Row 1 in Excel
    variable = 0
Here:

         ie.navigate "https://crownbet.com.au/sports/soccer" & variable

         Do While ie.Busy: DoEvents: Loop
         Do While ie.ReadyState <> 4: DoEvents: Loop

         Set doc = ie.document
         Set hTable = doc.getElementsByClassName("tableBody")

         For Each tb In hTable

            Set hBody = tb.getElementsByTagName("tbody")
            For Each bb In hBody

                Set hTR = bb.getElementsByTagName("tr")
                For Each tr In hTR

                     Set hTD = tr.getElementsByTagName("td")
                     y = 1 ' Resets back to column A
                     For Each td In hTD
                       ws.Cells(z, y).Value = td.innerText
                       y = y + 1
                     Next td
                     DoEvents
                     z = z + 1
                Next tr
                Exit For
            Next bb
        Exit For
      Next tb

    variable = variable + 40
    GoTo Here:


      End Sub

Also is there a way to make the data stop when no next button?

[kev_]

amend

ie.navigate "https://crownbet.com.au/sports/soccer" & variable

to

ie.navigate "https://crownbet.com.au/sports/soccer"

and it loads the correct page

Can't help you with the rest, sorry.

[CK76]

Use below code to export source code to text file. You can then study what's been returned from web service.

Sub Demo()
With CreateObject("MSXML2.serverXMLHTTP")
    .Open "GET", "https://crownbet.com.au/sports/soccer", False
    .setRequestHeader "Content-Type", "text/xml"
    .send
    x = .responseText
End With

Dim oPath As String: oPath = "C:\Test\crownbet.txt" 'Change path as needed
Dim intFF As Integer: intFF = FreeFile()
Open oPath For Output As #intFF
Print #intFF, x
Close #intFF

End Sub

It turns out that table is filled by function from JSON string.

You can find meat of data after "app.module.zendesk.zendeskController"

You can then use string manipulation to extract JSON string. Then use either RegExp, VBA-JSON or ScriptControl to parse JSON and output to Excel.

[Wheatly]

Hi CK76,

It appears I just get a error of Run-time error '76' path not found when tryimng to post this code into excel

Sub Demo()
With CreateObject("MSXML2.serverXMLHTTP")
    .Open "GET", "https://crownbet.com.au/sports/soccer", False
    .setRequestHeader "Content-Type", "text/xml"
    .send
    x = .responseText
End With

Dim oPath As String: oPath = "C:\Test\crownbet.txt" 'Change path as needed
Dim intFF As Integer: intFF = FreeFile()
Open oPath For Output As #intFF  [highlighted yellow]
Print #intFF, x
Close #intFF

End Sub

[CK76]

Dim oPath As String: oPath = "C:\Test\crownbet.txt" 'Change path as needed

As noted, change path if you don't have "C:\Test\" Folder.