Read out Content of HTML Table to Excel

[ancestetta]

Hey y'all,

I'm a little vba beginner, and want to read out the content of a table from an HTML page, and copy only the cells of the second row to excel.

I searched for long time in google, and finally I could write the following code. This script allows me to copy ALL rows, but I need only the second row. Could some body help me??

Thanks:



Sub test()
Set objExcel = ActiveWindow.ActiveSheet




Dim objIE
Dim varTables, varTable
Dim varRows, varRow
Dim varCells, varCell
Dim lngRow
Dim lngColumn
Dim strBuffer
Dim VINS
Dim URL

URL = "xxxxxxx"
Set objIE = CreateObject("InternetExplorer.Application")


With objIE
.Navigate URL
End With


While objIE.Busy
Wend
While objIE.Document.ReadyState <> "complete"
Wend

Set varTables = objIE.Document.All.tags("table")

For Each varTable In varTables


Set varRows = varTable.Rows
lngRow = 2 'This will be the first output row
For Each varRow In varRows

Set varCells = varRow.Cells
lngColumn = 1 'This will be the output column
For Each varCell In varCells
objExcel.Cells(lngRow, lngColumn) = varCell.innerText
lngColumn = lngColumn + 1
Next
lngRow = lngRow + 1
Next

Next

End Sub

 

[PHV]

For Each varRow In varRows
If lngRow = 3 Then
Set varCells = varRow.Cells
lngColumn = 1 'This will be the output column
For Each varCell In varCells
objExcel.Cells(lngRow, lngColumn) = varCell.innerText
lngColumn = lngColumn + 1
Next
Exit For
lngRow = lngRow + 1
End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ancestetta]

Thank you so much for your reply.
I'm sorry, but this doesn't work. lngRow is initialized to 2, and in your loop, you check if lngRow =3, and this will never be the case, since lngRow has not been incremented.

I think lngRow is the number of the row, not of the source html table, but of the destination excel table

 

[SkipVought]

Did you try moving the incriment stattement OUT of the If...End IF block, so that it would incriment?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I think the Enf If is in teh wrong place:

For Each varRow In varRows
    [blue]If lngRow = 3 Then[/blue]
        Set varCells = varRow.Cells
        lngColumn = 1 [green]'This will be the output column[/green]
        For Each varCell In varCells
            objExcel.Cells(lngRow, lngColumn) = varCell.innerText
            lngColumn = lngColumn + 1
        Next
        Exit For
    [blue]End If[/blue]
    lngRow = lngRow + 1
Next varRow

Have fun.

---- Andy

 

[PHV]

OOps, sorry for the typo.

 

[ancestetta]

Allright!! Thanks