[VBA] Data from Morningstar website to excel

[mikew8]

Hi,

Im very new to VBA and I'm trying to run a script which I found on this forum from 2012 posted by "Beeferino". I made 2 changes, the first being the URL as I don't think the original link works anymore and secondly I changed the word from InternetExplorer -> MicrosoftEdge, which I hope will not cause any problems.

Private Sub Worksheet_Change(ByVal Target As Range)
 
 If Target.Row = Range("Fund_Code").Row And _
 Target.Column = Range("Fund_Code").Column Then
  
  Dim IE As New MicrosoftEdge
   'ET.Visible = True
  IE.navigate "https://www.morningstar.ca/ca/report/fund/performance.aspx?t=0P000072KJ&FundServCode=" & Range("Fund_Code").Value
  Do
   DoEvents
  Loop Until IE.readyState = READYSTATE_COMPLETE
  Dim Doc As HTMLDocument
  Set Doc = IE.document
  
  Dim sAA As String
  sAA = Trim(Doc.getElementsByTagName("span")(20).textContent)
  Range("MER").Value = sAA
  
  Dim sAB As String
  sAB = Trim(Doc.getElementsByTagName("label")(0).textContent)
  Range("Fund_Name").Value = sAB
  
  Dim sAC As String
  sAC = Trim(Doc.getElementsByTagName("span")(30).textContent)
  Range("Investment_Style").Value = sAC
 End If
 
End Sub

I run it under my Sheet (not a Module). It throws an Compile Error: User-defined type not defined. I believe its purpose is when entering fund codes, to output specific values about each mutual fund (MER, Fund name etc.) from the Morningstar website.

If anyone could help me getting this code going, I'd really appreciate it as its a great learning experience in my area of interest.

Kindest Regards,
M

 

[lrobbo314]

Pretty sure you can't use any browser other than IE unless you use an add-in like Selenium. But this seems to work.

Private Sub Worksheet_Change(ByVal Target As Range)
 
 If Target.Row = Range("Fund_Code").Row And _
 Target.Column = Range("Fund_Code").Column Then
  
  Dim IE As Object: Set IE = CreateObject("InternetExplorer.Application")
   'ET.Visible = True
  IE.navigate "https://www.morningstar.ca/ca/report/fund/performance.aspx?t=0P000072KJ&FundServCode=" & Range("Fund_Code").Value
  Do
   DoEvents
  Loop Until IE.readyState = 4
  Dim Doc As Object
  Set Doc = IE.document
  
  Dim sAA As String
  sAA = Trim(Doc.getElementsByTagName("span")(20).textContent)
  Range("MER").Value = sAA
  
  Dim sAB As String
  sAB = Trim(Doc.getElementsByTagName("label")(0).textContent)
  Range("Fund_Name").Value = sAB
  
  Dim sAC As String
  sAC = Trim(Doc.getElementsByTagName("span")(30).textContent)
  Range("Investment_Style").Value = sAC
 End If
 
End Sub

 

[mikew8]

Thank you for your help, I'm getting a run-time error '1004: Method 'Range' of object'_Worksheet' failed.

I'm unsure how to fix it.


Mike