Question

make a table on daily item in HTML​

Answer 1

Answer:

Sub GetTableRow()

Dim ieApp As InternetExplorer

Dim ieDoc As Object

Dim ieTbl As Object

Dim i As Long, j As Long

Const sURL As String = “http://finance.yahoo.com/loan”

‘Create new IE instance

Set ieApp = New InternetExplorer

‘Go to url

ieApp.Visible = True

ieApp.Navigate sURL

‘Wait for page to load

Do

DoEvents

Loop Until ieApp.ReadyState = READYSTATE_COMPLETE

Set ieDoc = ieApp.Document

‘Loop through all the elements in the document via the ‘all’ property

For i = 0 To ieDoc.all.Length – 1

‘Only look at tables

If TypeName(ieDoc.all(i)) = “HTMLTable” Then

Set ieTbl = ieDoc.all(i)

‘I want to check the 3rd row (.Rows(2)) and will get an error if there

‘are less than three rows.

If ieTbl.Rows.Length > 2 Then

‘Here’s the text in the first cell of the third row that tells me

‘I have the right table

If ieTbl.Rows(2).Cells(0).innertext = “15-yr Fixed” Then

‘Loop through the cells and write to the sheet

For j = 0 To ieTbl.Rows(2).Cells.Length – 1

Sheet1.Cells(1, j + 1).Value = ieTbl.Rows(2).Cells(j).innertext

Next j

End If

End If

End If

Next i

‘Alternative method – when you figure out that i = 132 in the above loop

‘you can refer to that element directly. But beware, web sites change and

‘they don’t have the decency to even call you.

‘ Set ieTbl = ieDoc.all(132)

‘ For j = 0 To ieTbl.Rows(2).Cells.Length – 1

‘ Sheet1.Cells(1, j + 1).Value = ieTbl.Rows(2).Cells(j).innertext

‘ Next j

‘

ieApp.Quit

Set ieApp = Nothing

End Sub