VBA code to get specific web data: Error

[TimvMechelen]

Hi all,

I have a code for getting data from a specific website. But when I run it I get an error (Compile Error: User-Defined Type Not Defined) at "Dim html As New HTMLDocument"

Can somebody please help me with this problem?

Sub Get_Web_Data()

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim Followers As Variant


' Website to go to.
website = "https://soundcloud.com/timvanmechelen"


' Create the object that will make the webpage request.
Set request = CreateObject("MSXML2.XMLHTTP")


' Where to go and how to go there - probably don't need to change this.
request.Open "GET", website, False


' Get fresh data.
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"


' Send the request for the webpage.
request.send


' Get the webpage response data into a variable.
response = StrConv(request.responseBody, vbUnicode)


' Put the webpage into an html object to make data references easier.
html.body.innerHTML = response


' Get the followers from the specified element on the page.
Followers = html.getElementsByClassName("infoStats__value sc-font-tabular-light").Item(0).innerText


' Output the followers into a cell.
Sheets("Sheet1").Range("A1").Value = Followers


End Sub

 

[John_w]

Add a reference, via Tools > References in the VBA editor, to Microsoft HTML Object Library.

 

[TimvMechelen]

Thanks, but now I get the Run-Time Error '91': Object Variable or With Block Variable Not Set. Somebody know how can I fix this?

 

[John_w]

That error occurs because an element with the class name "infoStats__value sc-font-tabular-light" doesn't exist in the response returned by your XMLhttp request. Most of the data you see on the web page is generated by a separate Javascript request which your browser automatically runs and renders. Therefore requesting the URL with XMLhttp is insufficient and in this case it is easier to use IE automation to request the URL and extract the required data.

Public Sub IE_SoundCloud()

    Dim IE As Object
    Dim HTMLdoc As Object
    Dim followersDiv As Object
    
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .navigate "https://soundcloud.com/timvanmechelen"
        .Visible = True
        While .Busy Or .readyState <> 4: DoEvents: Wend
        Set HTMLdoc = .document
    End With
    
    Do
        Set followersDiv = HTMLdoc.getElementsByClassName("infoStats__value").Item(0)
        DoEvents
    Loop While followersDiv Is Nothing
    
    Sheets("Sheet1").Range("A1").Value = followersDiv.innerText
    
    IE.Quit 'close IE
    
End Sub

 

[TimvMechelen]

That error occurs because an element with the class name "infoStats__value sc-font-tabular-light" doesn't exist in the response returned by your XMLhttp request. Most of the data you see on the web page is generated by a separate Javascript request which your browser automatically runs and renders. Therefore requesting the URL with XMLhttp is insufficient and in this case it is easier to use IE automation to request the URL and extract the required data.

Public Sub IE_SoundCloud()

    Dim IE As Object
    Dim HTMLdoc As Object
    Dim followersDiv As Object
    
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .navigate "https://soundcloud.com/timvanmechelen"
        .Visible = True
        While .Busy Or .readyState <> 4: DoEvents: Wend
        Set HTMLdoc = .document
    End With
    
    Do
        Set followersDiv = HTMLdoc.getElementsByClassName("infoStats__value").Item(0)
        DoEvents
    Loop While followersDiv Is Nothing
    
    Sheets("Sheet1").Range("A1").Value = followersDiv.innerText
    
    IE.Quit 'close IE
    
End Sub

Thanks it works! But when I run the code it opens internet explorer. Is there a way to prevent this from happening?

 

[John_w]

It needs to run/open IE, but you can hide it by changing the .Visible line to:

        .Visible = False

 

[TimvMechelen]

It needs to run/open IE, but you can hide it by changing the .Visible line to:

        .Visible = False

Thanks! Do you know how I could get the date and time of the moment I run the macro in the Cell B1?

And the second time I run the macro the followers in cell A2 and the date and time in B2? (So the macro searches for the next free row)

 

[John_w]

Easier if you put column headings in A1:B1, then:

    With Worksheets("Sheet1")
        .Cells(.Rows.count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(followersDiv.innerText, Now)
    End With

 

[TimvMechelen]

Easier if you put column headings in A1:B1, then:

    With Worksheets("Sheet1")
        .Cells(.Rows.count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(followersDiv.innerText, Now)
    End With

Thanks!

 

[TimvMechelen]

Do you know how I can do this for facebook and instagram? Is it the same VBA code, but only another website?