How to separate html data into a table with VBA

Question

I'm trying to data scrape the main fundamentals table in finviz using

Public Sub TestRequest()
Dim Html As HTMLDocument, htable As HTMLTable
Set Html = New HTMLDocument
  
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "https://finviz.com/quote.ashx?t=AAPL", False
    .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
    .send
    Html.body.innerHTML = .responseText
End With

Sheets("Temp").Cells.ClearContents
Sheets("Temp").Cells(1, 1) = Html.getElementsByClassName("snapshot-table2")(0).innerText

However, the entire table data gets dumped into a single cell in Excel. How do I turn the data into a neat table?

user6585761 · Accepted Answer · 2021-04-19 01:40:05Z

1

The following code first transfers the data to your worksheet, and then creates a table...

Public Sub TestRequest()

    Sheets("Temp").Cells.ClearContents
    
    Dim Html As HTMLDocument
    Set Html = New HTMLDocument
      
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://finviz.com/quote.ashx?t=AAPL", False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send
        Html.body.innerHTML = .responseText
    End With
    
    Dim htable As HTMLTable
    Set htable = Html.getElementsByClassName("snapshot-table2")(0)
    
    Dim r As Long
    Dim c As Long
    
    With htable
        For r = 0 To .Rows.Length - 1
            For c = 0 To .Rows(r).Cells.Length - 1
                Sheets("Temp").Cells(r + 1, c + 1).Value = .Rows(r).Cells(c).innerText
            Next c
        Next r
    End With
    
    Sheets("Temp").ListObjects.Add _
        SourceType:=xlSrcRange, _
        Source:=Sheets("Temp").Range("A1").CurrentRegion, _
        xllistobjecthasheaders:=xlNo
    
End Sub

answered Apr 19, 2021 at 1:40

user6585761

Sign up to request clarification or add additional context in comments.

1 Comment

acculum Over a year ago

Exactly what I needed. Thanks!

Collectives™ on Stack Overflow

How to separate html data into a table with VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related