Get Element by ID from inside an iframe

Question

I'm trying to copy the last price of share prices into an Excel worksheet from

<div id="last-price-value" vkey="LastPrice">127.36</div>.

Internet Explorer opens and navigates to the site but the last price isn't copied to my worksheet. I added Debug.Print element.innertext but nothing appears in the immediate pane.

Dim element As IHTMLElement
Dim elements As IHTMLElementCollection

Dim ie As New InternetExplorer
Dim html As HTMLDocument

my_page = "http://www.morningstar.com/stocks/XASX/COH/quote.html"

With ie
    .Visible = TRUE
    .Navigate my_page

    Do While ie.readyState <> READYSTATE_COMPLETE
    Loop

    Set html = ie.document
    Set element = html.getElementById("last-price-value")    
    Debug.Print element.innertext

    Sheets("Sheet 1").Range("A2").Value = element.innertext     
End With

Answer 1

So the issue is that the last-price-value element is contained within an iFrame- iFrames are a little bit of a pain and require special syntax and a few extra steps to retrieve their data- Basically you want to find the iFrame, get its HTML using .ContentDocument, and then can use that HTML document just like you normally would... however, this case of extracting data from an iFrame is particularly painful because the main web page is on one domain where the iFrame is on another domain, preventing you from accessing the iFrame's HTML from the main page... SO, what you have to do instead is: 1- Load the main page 2- Find the frame 3- Get the frame's URL 4- Load the frame URL 5- Pull the last-price-value..

See code example below:

Public Sub sampleCode()
Dim IE As New InternetExplorer
Dim HTMLDoc As HTMLDocument
Dim parentURL As String
Dim frames As IHTMLElementCollection
Dim frameURL As String
Dim frameHTML As HTMLDocument
Dim fCounter As Long
Dim targetElement As HTMLObjectElement

parentURL = "http://www.morningstar.com/stocks/XASX/COH/quote.html"
'1- Load the main page
With IE
   .Visible = False
   .Navigate parentURL
    While .Busy Or .readyState <> READYSTATE_COMPLETE: Wend
    Set HTMLDoc = IE.document
End With

'2- Find the frame
Set frames = HTMLDoc.getElementsByTagName("iframe")
'Loop through all the frames
For fCounter = 0 To frames.Length - 1
    'Test each frame's ID to find the correct one (numerical appendix to ID can change so need to test for pre-fix)
    If Left(frames(fCounter).ID, 10) = "QT_IFRAME_" Then
        '3- Get the frame's URL
        frameURL = frames(fCounter).src
        Exit For
    End If
Next

'4- Load the frame URL
With IE
   .Navigate frameURL
    While .Busy Or .readyState <> READYSTATE_COMPLETE: Wend
    Set frameHTML = IE.document
End With

'Find and pull the last price
Set targetElement = frameHTML.getElementById("last-price-value")
Sheets("Sheet 1").Range("A2").Value = CDbl(targetElement.innerText)

IE.Quit
Set IE = Nothing
End Sub

Also, Yahoo has pretty easy VBA interface to pull stock prices so unless you need Morningstar because you are pulling prices on obscure mutual funds or private funds I would say you could make it easier on yourself by switching to Yahoo sources..

Hope this helps, TheSilkCode