Web Query doesn't load for the website

[staygreen1980]

Hi Everyone,

I am trying to do a web query to extract information from this link

National Basketball Association Ranking & Standings & Table 2023-2024 - Bola Livescore & Orlando Magic vs Charlotte Hornets H2H, Odds, Standings - Bola Livescore (The 1st table "Live Odds Comparison"

There is nothing that shows up when I load the link.

I have tried the web query options, tried loading at different timings, basically everything I can think of in the past 3-4 weeks.

I will be grateful if someone can give me advice, and I will be glad to give a small token if someone can enlighten me.

Many thanks.

 

[John_w]

I think Excel's Web Query uses Internet Explorer to load the website, however some websites don't work in IE because it doesn't support modern web standards. The Web View tab shows the data table hasn't loaded and you get the same problem if you load the website in IE.



One solution is to load the website in Edge and use the Selenium library to scrape the table of data into Excel.

First, you must install SeleniumBasic and Microsoft Edge WebDriver.

Instructions for installing SeleniumBasic and Microsoft Edge WebDriver:

1. SeleniumBasic
Download latest .exe from Seleniumbasic.
Run .exe to install SeleniumBasic in %localappdata%\SeleniumBasic (C:\Users\YourUserName\AppData\Local\SeleniumBasic).

2. Microsoft Edge WebDriver
Download Stable Channel x64 version (or x86 for 32-bit Windows) .zip file from Microsoft Edge WebDriver | Microsoft Edge Developer - the version which matches your Edge browser version.
Extract msedgedriver.exe from the .zip and copy as edgedriver.exe to %localappdata%\SeleniumBasic (C:\Users\YourUserName\AppData\Local\SeleniumBasic), overwriting existing
older version, if any.

Here is the macro which automates Edge using the Selenium library and extracts the table of data from the first website.

Option Explicit

Dim driver As Selenium.EdgeDriver

Public Sub Edge_Extract_Table_Data()

    Dim div As WebElement
    Dim dataTable As WebElement
    Dim tRow As WebElement
    Dim destCell As Range
    Dim r As Long, c As Long

    With ThisWorkbook.Worksheets(1)
        .Activate
        .Cells.Delete
        Set destCell = .Range("A1")
    End With

    Set driver = New Selenium.EdgeDriver
    With driver
        .Start
        .Get "https://basketball.bola010.com/leaguerank/1"
    End With
    
    '<div id="standingdiv">
    ' <table width="100%" border="1" cellpadding="2" cellspacing="0" class="tdlink">

    Set div = driver.FindElementById("standingdiv")
    Set dataTable = div.FindElementByTag("TABLE")
    
    r = 1
    Set tRow = dataTable.FindElementsByTag("TR")(r)
    For c = 1 To tRow.FindElementsByTag("TH").Count
        destCell.Offset(r - 1, c - 1).Value2 = tRow.FindElementsByTag("TH")(c).Text
    Next
    
    For r = 2 To dataTable.FindElementsByTag("TR").Count - 1
        Set tRow = dataTable.FindElementsByTag("TR")(r)
        For c = 1 To tRow.FindElementsByTag("TD").Count
            destCell.Offset(r - 1, c - 1).Value = "'" & tRow.FindElementsByTag("TD")(c).Text
        Next
    Next

End Sub

A final step, in order to compile and run the macro is to set a reference to Selenium Type Library in the VBA editor - Tools -> References.