How to get specific Data from Website (HTML)?

Question

I am trying to extract specific data from a website and load it into my Excel worksheet.

For example, I want to extract the Metascore from https://www.metacritic.com/game/sid-meiers-civilization-vi/. This information is located within a specific element.

The element I want to extract

Private Sub URL_Load(ByVal sURL As String)
    'Variablen deklarieren
    Dim appInternetExplorer As Object
    Dim htmlTxt As String
    Dim spanTxt2 As String
    
    Set appInternetExplorer = CreateObject("InternetExplorer.Application")
    appInternetExplorer.navigate sURL
    Do: Loop Until appInternetExplorer.Busy = False
    Do: Loop Until appInternetExplorer.Busy = False
    spanTxt = appInternetExplorer.document.DocumentElement.all.tags("SPAN")
    'objSelect = appInternetExplorer.document.DocumentElement.all.tags("SPAN")
    Debug.Print htmlTxt
    Set appInternetExplorer = Nothing
    Close
    'Mache hier irgendwas mit dem Text: Parsen, ausgeben, speichern
    MsgBox "Der Text wurde ausgelesen!"
End Sub

In this code, the variable "spanTxt" is described with the following value: X. Unfortunately, this is not the element I want to extract.

How can I extract a specific element?

I tried:

htmlTxt = appInternetExplorer.document.DocumentElement.outerHTML
htmlTxt1(1) = appInternetExplorer.document.DocumentElement.innerHTML
htmlTxt2 = appInternetExplorer.document.DocumentElement.innerText

Under the Data tab in excel, there's an option "From Web" where you can query a website on specific spots on the page. Is this something that could help you sidestep the vba to get what you need? — Mark S.
– Mark S., Commented Jan 17, 2024 at 21:52
IE is too old to support the web. appInternetExplorer.documen is blank. You can check it in VBE Locals Window. — taller
– taller, Commented Jan 17, 2024 at 23:06

Spectral Instance · Accepted Answer · 2024-01-18 02:05:18Z

2

Since you're querying a static page, you don't actually have to be familiar with any web technologies or use any external objects:-

Public Sub ImportWebPage()
    Dim ws As Worksheet: Set ws = Sheet1    'change as required'
    With ws.QueryTables.Add("URL;https://www.metacritic.com/game/sid-meiers-civilization-vi/", ws.Range("A1"))
        .WebSelectionType = xlEntirePage
        .BackgroundQuery = False
        .Refresh
    End With
    Dim metascore As Double
    With ws
        metascore = .Columns(1).Find("meta").Offset(1).Value
        .Columns(1).Clear
        .QueryTables(1).Delete
    End With
    MsgBox "Metascore is " & metascore
End Sub

Note that this approach imports the full html (so you need a 'spare' range on which to store it) and then uses regular functionality to locate the metascore.

answered Jan 18, 2024 at 2:05

Spectral Instance

2,7792 gold badges5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zwenn · Accepted Answer · 2024-01-18 08:25:50Z

Don't use the IE anymore. It's outdated. Here is an example how to get the wanted value with xhr (XML HTTP Request)

Sub ExampleToGetMetaScore()

    Const url As String = "https://www.metacritic.com/game/sid-meiers-civilization-vi/"
    Dim doc As Object
    Dim nodeScore As Object
    
    Set doc = CreateObject("htmlFile")
    
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "GET", url, False
        .send
        
        If .Status = 200 Then
            doc.body.innerHTML = .responseText
            Set nodeScore = doc.getElementsByClassName("c-productScoreInfo_scoreNumber")(0)
            MsgBox nodeScore.innerText
        Else
            MsgBox "Page not loaded. HTTP status " & .Status
        End If
    End With
End Sub

You write that you are a beginner in VBA. But VBA is not enough for WebScraping. You also need to be familiar with the following technologies:

xhr (to download a file from a web server)
SeleniumBasic (automate Chrome/Edge if xhr does not work)
HTML (to localize the contents of a page in the structure and find a way to address them)
DOM for HTML (provides methods like the used getElementsByClassName())
DOM = Document Object Model

Collectives™ on Stack Overflow

How to get specific Data from Website (HTML)?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related