2

In a previous answer (vba - html table to excel worksheet) about parsing/pasting HTML table contents into an Excel sheet, wbeard2 shared this very helpful, illustrative piece of code. He/she notes that it implants the table data into Excel but not the headings. I was wondering how to amend this code to include the columnar headings in the Excel sheet also. Was thinking there might be a way to loop through heading elements similar to that being shown for looping through all 's and cells within the rows in the 's, but not sure if there is an equivalent looping element for headings -- maybe 's? Any advice/guidance on this appreciated.

Here was the sample code from the previous answer referenced above:

Private Sub Test()

Dim ie As Object, i As Long, strText As String

Dim doc As Object, hTable As Object, hBody As Object, hTR As Object, hTD As Object
Dim tb As Object, bb As Object, tr As Object, td As Object

Dim y As Long, z As Long, wb As Excel.Workbook, ws As Excel.Worksheet

 Set wb = Excel.ActiveWorkbook
 Set ws = wb.ActiveSheet

 Set ie = CreateObject("InternetExplorer.Application")
 ie.Visible = True

  y = 1   'Column A in Excel
  z = 1   'Row 1 in Excel

 ie.navigate "http://", , , , "Content-Type: application/x-www-form-urlencoded" & vbCrLf

 Do While ie.busy: DoEvents: Loop
 Do While ie.ReadyState <> 4: DoEvents: Loop

 Set doc = ie.document
 Set hTable = doc.GetElementsByTagName("table")


 For Each tb In hTable

    Set hBody = tb.GetElementsByTagName("tbody")
    For Each bb In hBody

        Set hTR = bb.GetElementsByTagName("tr")
        For Each tr In hTR


             Set hTD = tr.GetElementsByTagName("td")
             y = 1 ' Resets back to column A
             For Each td In hTD
               ws.Cells(z, y).Value = td.innertext
               y = y + 1
             Next td
             DoEvents
             z = z + 1
        Next tr
        Exit For
    Next bb
Exit For
Next tb

End Sub

0

You must log in to answer this question.

Browse other questions tagged .