VBA spliting results from html imported table into

Question

Hi I am importing a whole table from a website to excel string:

Dim fST As String
fST = Doc.getElementsByTagName("table")(0).innerText

after that I would like to split the table inside excel cells and the splitting to be done using the <td> tags from the html table, or at least this is the option for which I think can be done so the imported table will be the same inside excel once it is imported every value will be inside individual cell.

Let me know thanks.

Here is the Whole conde that I am using:

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Row = Range("URL").Row And _
 Target.Column = Range("URL").Column Then
    Dim IE As New InternetExplorer
    IE.Visible = True
    IE.navigate Application.ActiveSheet.Range("URL")
    Do
        DoEvents
        Loop Until IE.readyState = READYSTATE_COMPLETE
    Dim Doc As HTMLDocument
    Set Doc = IE.document

    Dim tbl, trs, tr, tds, td, r, c

    Set tbl = Doc.getElementsByTagName("table")(0)
    Set trs = tbl.getElementsByTagName("tr")

    For r = 1 To trs.Count
        Set tds = trs(r).getElementsByTagName("td")
        For c = 1 To tds.Count
            ActiveSheet.Cells(r, c).Value = tds(c).innerText
        Next c
    Next r
    IE.Quit
 End If
End Sub

But it says error: Object doesn't support this property or method on the following line: For r = 1 To trs.Count

Answer 1

EDIT: tested example

Sub Tester()

Dim IE As Object
Dim tbls, tbl, trs, tr, tds, td, r, c

Set IE = CreateObject("internetexplorer.application")

IE.navigate "http://www.w3schools.com/html/html_tables.asp"

Application.Wait Now + TimeSerial(0, 0, 4)

    Set tbls = IE.Document.getElementsByTagName("table")
    For r = 0 To tbls.Length - 1
        Debug.Print r, tbls(r).Rows.Length 
    Next r

    Set tbl = IE.Document.getElementsByTagName("table")(5)
    Set trs = tbl.getElementsByTagName("tr")

    For r = 0 To trs.Length - 1
        Set tds = trs(r).getElementsByTagName("td")
        'if no <td> then look for <th>
        If tds.Length = 0 Then Set tds = trs(r).getElementsByTagName("th")

        For c = 0 To tds.Length - 1
            ActiveSheet.Range("B4").Offset(r, c).Value = tds(c).innerText
        Next c
    Next r

End Sub

Answer 2

I looked all over for the answer to this question, too. I finally found the solution by talking to a coworker which was actually through recording a macro.

I know, you all think you are above this, but it is actually the best way. See the full post here: http://automatic-office.com/?p=344 In short, you want to record the macro and go to data --> from web and navigate to your website and select the table you want. Tell excell which cell to put it in and thats it!

I have used the above solutions "get element by id" type stuff in the past, and it is great for a few elements, but if you want a whole table, and you aren't super experienced, just record a macro. don't tell your friends and then reformat it to look like your own work so no one knows you used the macro tool ;)