Lottery results Belgium

[jacafate]

Hi,
Im creating an excel sheet to calculate the profit of the lottery.
I want to import the winning numbers and the prize breakdown into my excel automatically.

The website is located here : Lotto uitslagen - zaterdag 02 maart 2024 - Nationale Loterij
This is the info I need in my Excel

I already tried to create a macro with a save mhtml file. Here is the code.

But it don't find the numbers in this file.

Perhaps someone has another solution to accomplish this ?

Sub ExtractULDataToExcel()
Dim xmlHTTP As Object
Dim htmlDoc As Object
Dim ulElements As Object
Dim ulElement As Object
Dim liElements As Object
Dim liElement As Object
Dim rowIndex As Integer

' Create a new instance of MSXML2.ServerXMLHTTP
Set xmlHTTP = CreateObject("MSXML2.ServerXMLHTTP")

' Define the URL of the webpage containing the HTML content
xmlHTTP.Open "GET", "Lotto uitslagen - woensdag 28 februari 2024 - Nationale Loterij", False
xmlHTTP.send

' Wait for the webpage to load
Do While xmlHTTP.readyState <> 4 ' 4 means complete
Application.Wait Now + TimeValue("0:00:05") ' Wait for 5 seconds
Loop

' Save the webpage as MHTML file
Dim fileNumber As Integer
fileNumber = FreeFile
Open "c:\temp\webpage.mhtml" For Output As #fileNumber
Print #fileNumber, xmlHTTP.responseText
Close #fileNumber

' Create a new instance of Internet Explorer
Set htmlDoc = CreateObject("htmlfile")
htmlDoc.body.innerHTML = xmlHTTP.responseText

' Load the saved MHTML file
htmlDoc.Open "webpage.mhtml"

' Get all <ul> elements with the specified class
Set ulElements = htmlDoc.getElementsByTagName("ul")

' Initialize row index for Excel
rowIndex = 1

' Loop through each <ul> element
For Each ulElement In ulElements
' Check if the <ul> element has the desired class
If ulElement.className = "lnl-draw-numbers " Then
' Get all <li> elements within the <ul> element with the desired class
Set liElements = ulElement.getElementsByClassName("lnl-draw-numbers__winning-number")

' Loop through each <li> element and extract its text
For Each liElement In liElements
' Output text to Excel
ThisWorkbook.Sheets("Sheet1").Cells(rowIndex, 1).Value = liElement.innerText
rowIndex = rowIndex + 1
Next liElement
End If
Next ulElement

' Clean up
Set xmlHTTP = Nothing
Set htmlDoc = Nothing
End Sub

Any help would be thankfull

Kind Regards,
Jacafate

 

[alansidman]

A simpler means would be with Power Query aka Get and Transform Data and found on the Data Tab of the Ribbon.
Data-->Get and Transform Data-->From Web
Enter the URL
PQ will go to the web site and give you a choice of which table to select.

 

[jacafate]

A simpler means would be with Power Query aka Get and Transform Data and found on the Data Tab of the Ribbon.
Data-->Get and Transform Data-->From Web
Enter the URL
PQ will go to the web site and give you a choice of which table to select.

Thanks Alansidman, already tried this but this doesn't returns me anything to select, strange (the exact website is Lotto uitslagen - zaterdag 02 maart 2024 - Nationale Loterij)