vbawebweb-scrapingmshtml

Scraping Data from Website using vba doesn´t work


I want to create a list of player names scraping a website. The Internet Explorer starts but I get an run time error "438" - Object does not support this property or method.

Structure of webpage

Structure of webpage

My coding is as follows:

Option Explicit

Sub Kickbase()

Dim IE As New SHDocVw.InternetExplorer
Dim HTMLdoc As MSHTML.HTMLDocument
Dim HTMLPlayers As MSHTML.IHTMLElementCollection
Dim HTMLPlayer As MSHTML.IHTMLElement
Dim i As Integer
Dim HTMLfirstName As Object
Dim firstName As String


IE.Visible = True
IE.Navigate "https://play.kickbase.com/transfermarkt/kaufen"

Do While IE.ReadyState <> READYSTATE_COMPLETE
Loop

Application.Wait (Now + TimeValue("0:00:10"))

Set HTMLdoc = IE.Document

Set HTMLPlayers = HTMLdoc.getElementsByClassName("players")

For i = 0 To HTMLPlayers(0).getElementsByClassName("firstName").Length - 1

Set HTMLfirstName = HTMLPlayers(0).getElementsByClassName("firstName")

   If Not HTMLfirstName Is Nothing Then
    firstName = Trim(HTMLfirstName.innerText)
    
   Else
     firstName = "no_value"
   End If

Debug.Print firstName

Next i

End Sub

I have activated the following libraries: enter image description here


Solution

  • Since it's not possible to test the website on my own, the code below might not be the best way to do it but it should work:

    Sub Kickbase()
    
        Dim IE As New SHDocVw.InternetExplorer
        Dim HTMLdoc As MSHTML.HTMLDocument
        Dim HTMLPlayers As Object
        Dim i As Integer
        Dim firstName As String
            
        IE.Visible = True
        IE.navigate "https://play.kickbase.com/transfermarkt/kaufen"
        
        Do While IE.readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
        
        Application.Wait (Now + TimeValue("0:00:10"))
        
        Set HTMLdoc = IE.document
        Set HTMLPlayers = HTMLdoc.getElementsByClassName("playerName")
                
        For i = 0 To HTMLPlayers(0).getElementsByClassName("firstName").Length - 1
            
            firstName = Trim$(HTMLPlayers(0).getElementsByClassName("firstName")(i).innerText)
            If firstName = vbNullString Then firstName = "no_value"
                
            Debug.Print firstName
        Next i
        
        '=== Optional depending on your use case, remember to close IE or else it will remain there ===
        'IE.Quit
        'Set IE = Nothing
        
    End Sub