Excel VBA Scraping - Morningstar

Question

I want to scape the financials on this link with an excel vba code , however unable to do this with my current code.

All I want to copy and paste the all of the data. It doesn't matter if it containes other information or not.

How can I do this?

Link:http://financials.morningstar.com/cash-flow/cf.html?t=SPN&region=usa&culture=en-US Sub MS()

Sheets("Morningstar").ClearContents my_Page = "http://financials.morningstar.com/cash-flow/cf.html?t=SPN&region=usa&culture=en-US"

Sub MS()

Sheets("Morningstar").ClearContents
    my_Page = "http://financials.morningstar.com/cash-flow/cf.html t=SPN&region=usa&culture=en-US"
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate my_Page
        Do Until .ReadyState = 4: DoEvents: Loop
    End With

   Application.EnableEvents = False
   IE.ExecWB 17, 0
   Do Until IE.ReadyState = 4: DoEvents: Loop
   IE.ExecWB 12, 2
   ActiveSheet.PasteSpecial Format:="HTML", link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
   Range("A1").Select

   IE.Quit
    Application.EnableEvents = True
End Sub

http://financials.morningstar.com/cash-flow/cf.html?t=SPN&region=usa&culture=en-US — nily, Mar 30 '15 at 13:40

score 1 · Accepted Answer · edited May 23 '17 at 12:15

One way is to use the MSHTML library (you will need to enable this in references).

Load the html document into a object. IE.document Loop through the elements to extract the data you want.

Here are some good examples of parsing the information using VBA. You can use these techniques to get your data: Parse HTML content in VBA Pulling a table out of a mess of HTML w/ VBA & Excel http://www.ozgrid.com/forum/showthread.php?t=184695

Good luck. Remember to review regularly as site changes can break scraping code.

Excel VBA Scraping - Morningstar

1 Answers1