Web Scrape Error : getElementsByClassName

[Excel_Novice_123]

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.

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]

Have you looked at the HTML returned by your request? It includes JavaScript code and data which prevents non-browser requests from seeing the web data. The HTML doesn't contain elements with the class name "flexposts__nowrap flexposts__time nowrap", hence the error.

The easiest solution is to use IE or Chrome to scrape the data. Slower than XMLhttp requests, but they automatically decode the JavaScript, giving you the web data.

 

[diddi]

the site has request cookies which are not managed by XMLHTTP. it must have been recoded recently if it used to work

 

[Excel_Novice_123]

Thanks very much John_w and Diddi for the responses above, that's very helpful.

Please could i ask if you would have an example of the code that shows how to scrape with IE or Chrome? Thanks

 

[diddi]

do a search for posts concerning 'selenium' it is the chrome automation tool. not something i have experience in tho

 

[Worf]

A Selenium thread:

import data from Web

Dear ALL, I follow your video, I want to import data from Web of this link https://racing.hkjc.com/racing/information/english/Racing/JockeysRides.aspx When I run from web, in explore I read the link, document, does not have TABLE, but I can read table and data, on TABLE VIEW and DATA VIW, when...

www.mrexcel.com

 

[diddi]

@Worf. is there a thread on installing selenium for win10. i had a try and i cant seem to see it in excel

 

[Worf]

It is midnight and I am going offline now, will be back tomorrow...

 

[Dan_W]

When I came across Selenium, I used a helpful guide that I can now no longer find - the good news is that Wise Owl recently produced a video on getting started with Selenium (Youtube).
Basically, you need to:

1. Download the executable (see Releases)
2. Install SeleniumBasic
3. Download ChromeDriver (or whatever driver you need for whichever browser you want to use) - you need to make sure that the version of ChromeDriver matches your version of Chrome (v89 = v89).
4. Once the ChromeDriver.EXE file has downloaded, you need to help Selenium find it. According to Wise Owl (See here), you need to copy/move the ChromeDriver to the folder containing SeleniumBasic. I'm don't think that's the only way of doing it - according to the website, you just need to include the ChromeDriver location in your PATH environment variable (instructions).
5. You will need to add Selenium Basic to your project by adding it as a reference in the VBE.

I'd recommend looking at the VBScript examples provided in the package. Note that some of the examples use different drivers (for Firefox etc), so don't be surprised if they don't work - you will need to adjust the code to makeit work with your particular driver.

 

[diddi]

thx. i got the example running under chrome