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