Excel_Novice_123
New Member
- Joined
- Apr 2, 2021
- Messages
- 2
- Office Version
- 365
- Platform
- 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.
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