Question 209780 in Html Question posted in
Some basic documentation can be found here.

Html – Web Scraping via Excel VBA

I am trying to set up a macro within VBA to pull a price from a specified website and update the price within my worksheet. I am not very experienced in VBA or HTML, so I followed some tutorials on YouTube to get started.

I followed the tutorial exactly but changed the website. I am getting the run time error 424 object required when running my code.

When I debug the error, it says that price is empty. I tried to use the class closest to the value I want to return to excel. Expanding on the class that I put in my code it shows the value of the price I want to return as a text. So my code should recognize the text and assign it to price as far as I am aware. I’m not too sure where to go from here. Any help would be greatly appreciated. I’ll post my code below:

Sub Get_Web_Data()

Dim request As Object
Dim response As String
Dim htlm As New HTMLDocument
Dim website As String
Dim price As Variant

'Website to go to.
website = "https://www.taptools.io/portfolio"

'Create the object that will make the webpage request.

Set request = CreateObject("MSXML2.XMLHTTP")

'Where to go and how to go there
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

'Put the wepage into an html object to make data references easier.

response = StrConv(request.responseBody, vbUnicode)

'Get the price from the specified element on the page.

price = HTML.getElementsByClassName("portfolio__table__content__right-align portfolio__table__content__stack portfolio__table__content__price")(0).innerText

'Output the price into a message box.
MsgBox price

End Sub
Tags:

2

Answers


  1. 0

    I’m no expert, but if this helps:

    You have a typo in your definitions. You have htlm instead of html. Then later, you also have the response text going into the string "response" but you never convert that to an HTML DOM Object for use with the html variable. Maybe add this after that "
    response = StrConv(……" line.

    html.body.innerHTML = response

    Login or Signup to reply.
  2. 0

    Behind the posted url is a login page. I think you want the price from a page behind the login. So I can’t test if the following code will work. Espacialy I don’t know if this line will do what you want:
    price = doc.getElementsByClassName("portfolio__table__content__right-align portfolio__table__content__stack portfolio__table__content__price")(0).innerText

    It is possible that the price is not avilable in the downloaded html document because xhr (XML HTTP Request) can only handle static documents. If the price will be placed by JavaScript to the html code, you must find another way th get it. In that case you will get the run time error 424 object required again.

    Sub Get_Web_Data()

  Dim url As String
  Dim doc As Object
  Dim price As String
  
  url = "https://www.taptools.io/portfolio"
  Set doc = CreateObject("htmlFile")
  
  With CreateObject("MSXML2.XMLHTTP.6.0")
    .Open "GET", url, False
    .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
    .send
    
    If .Status = 200 Then
      doc.body.innerhtml = .responseText
      price = doc.getElementsByClassName("portfolio__table__content__right-align portfolio__table__content__stack portfolio__table__content__price")(0).innerText
    Else
      MsgBox "Page not loaded. HTML status: " & .Status
    End If
  End With
  
  MsgBox price
End Sub
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search