Web Scraping via Excel VBA

Question

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

This is a bad way to do it, you'll face so many issues. Excel VBA has the old IE6 WebBrowser and none of todays websites work well in it. Also please take the time to learn how to format your questions. PS This might help https://stackoverflow.com/a/11174989/495455 — Jeremy Thompson, Jul 17 '23 at 04:30
I would not use VBA but power query inside of excel instead. Example [bitcoin](https://stackoverflow.com/questions/70908743/how-to-get-bitcoin-price-using-power-query) , Another [investing data](https://stackoverflow.com/a/70089754/5091720) — Shane S, Jul 17 '23 at 05:26
Another better tool is python. If the data is coming from html and it's in tables already, Then you can use pandas [pd.read_html](https://pandas.pydata.org/docs/reference/api/pandas.read_html.html) — Shane S, Jul 17 '23 at 05:31

score 0 · Answer 1 · answered Jul 17 '23 at 05:24

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

answered Jul 17 '23 at 05:24

ninthbit

1
3

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 17 '23 at 15:18

score 0 · Answer 2 · answered Jul 17 '23 at 14:15

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

Web Scraping via Excel VBA

2 Answers2