CODEWITHSUNDEEP
excelvba
KenWong
KenWong

Reputation: 11

Web scraping with VBA

I would like to create a macro to get real time stock quote from a financial website. Below is my code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim stock As Long, rng As Range, quote As String, ie As InternetExplorer, doc As HTMLDocument

Set rng = Range("A1")
stock = rng.Value
Set ie = CreateObject("InternetExplorer.Application")

If Target.Rows = rng.Rows And Target.Columns = rng.Columns Then

    ie.navigate "http://www.aastocks.com/en/ltp/rtquote.aspx?symbol=0" & rng.Value
    ie.Visible = True

    Do
        DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

    Set doc = ie.document

    quote = doc.getElementsByTagName("neg bold").innertext

    MsgBox quote

End If

End Sub

However when I run the macro, it shows error 91 (Object variable or With block variable not set) in the line

quote = doc.getElementsByTagName("neg bold").innertext

below is the HTML code of the source (the stock price)

<span class="neg bold">1.900</span>

Thanks a lot!

Upvotes: 1

Views: 365

Answers (1)

IAmDranged
IAmDranged

Reputation: 3020

The argument for the getElementsByTagName should be an element name - not a class name. If you are looking to target an element by its class name, you could use the getElementsByClassname method instead

Also, be aware both these methods return elements collections - so unless in this specific instance the collection returned boils down to the single element you are targetting, there may be a bit more work to be done in order to narrow it down to what you are looking for

Upvotes: 2

Related Questions