Web Scrape Error : getElementsByClassName

Excel_Novice_123

New Member
Joined
Apr 2, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Really grateful if someone can help me with the below. I am trying to get some news links from the website in the below code. It previously used to work fine and recently is causing issues, but I can't seem to figure out why.

In terms of what the code should be doing is extracting the timestamp for the fist 5 news articles in column A. Thereafter in columns b and c extracting the headline and the reference website for each of the first 5 articles.

When i run the code the first For Next loop gives an error "Object variable or with block variable not set".

If I skip over this the second For Next loop gives blank output and doesn't retrieve anything from the website.


VBA Code:
Sub Get_Web_Data()

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim timeStamp As String

website = "https://www.forexfactory.com/news"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", website, False
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response

For i = 1 To 5
timeStamp = html.getElementsByClassName("flexposts__nowrap flexposts__time nowrap").Item(i - 1).innerText
ActiveSheet.Cells(i + 1, 1).Value = Trim(timeStamp)
Next

Set my_data = html.getElementsByClassName("flexposts__title title")
Dim link
i = 1
For Each elem In my_data
 Set link = elem.getElementsByTagName("a")(0)
 i = i + 1
 If i > 6 Then
 Exit For
 End If
 ActiveSheet.Cells(i, 3).Value = link.href
 ActiveSheet.Cells(i, 2).Value = link.innerText
Next

End Sub
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,764
I don't see the point of using Chrome/Selenium, with its installation overheads, if the site works with IE.
VBA Code:
Public Sub Extract_News_Articles()

    Dim IE As Object 'InternetExplorer
    Dim HTMLdoc As Object 'HTMLDocument
    Dim timestamps As Object 'IHTMLElementCollection
    Dim titles As Object 'IHTMLElementCollection
    Dim link As Object 'HTMLAnchorElement
    Dim destCell As Range, i As Long
        
    With ActiveSheet
        .Cells.Clear
        .Range("A1:C1").Value = Array("Timestamp", "Headline", "Link")
        Set destCell = .Range("A2")
    End With
    
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .navigate "https://www.forexfactory.com/news"
        While .Busy Or .readyState <> 4: DoEvents: Wend
        Set HTMLdoc = .document
    End With
    
    Set timestamps = HTMLdoc.getElementsByClassName("flexposts__nowrap flexposts__time nowrap")
    Set titles = HTMLdoc.getElementsByClassName("flexposts__title title")
    
    For i = 0 To 4
        Set link = titles(i).getElementsByTagName("A")(0)
        destCell.Offset(i, 0).Value = timestamps(i).innerText
        destCell.Offset(i, 1).Value = link.innerText
        destCell.Offset(i, 2).Value = link.href
    Next
    
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Watch MrExcel Video

Forum statistics

Threads
1,130,136
Messages
5,640,324
Members
417,137
Latest member
Dashivas123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top