Getting table data off a website

[patricklee]

Oracle Corp, ORCL:NYS financials - FT.com

I would like to grab data off a website but my code only manage to get the data and missed out on the last two columns of the table. Could anyone help me get the last two columns in? PLEASE!

Sub Get_Fin_Data()


Dim strUrl As String
Dim oXML As MSXML2.XMLHTTP
Dim oHTML As MSHTML.HTMLDocument
Dim lngRow As Long
Dim lngCol As Long
Dim lngTable As Long
Dim lngX As Long


Dim stockcode As String
stockcode = Sheets("Sheet1").Range("B1").Value
strUrl = "http://markets.ft.com/research/Markets/Tearsheets/Financials?s=" & stockcode & ":NYS&subview=IncomeStatement"


Set oXML = New MSXML2.XMLHTTP
Set oHTML = New MSHTML.HTMLDocument
With oXML
.Open "GET", strUrl, False
.send
oHTML.Body.innerHTML = .responseText
End With
lngX = 0
With oHTML.getElementsByTagName("table")
For lngTable = 0 To .Length - 1
For lngRow = 0 To .Item(lngTable).Rows.Length - 1
For lngCol = 0 To .Item(lngTable).Rows(lngRow).Cells.Length - 1
Range("A2").Offset(lngRow + lngX, lngCol).Value = .Item(lngTable).Rows(lngRow).Cells(lngCol).innerText
Next lngCol
Next lngRow
lngX = lngRow + lngX
Next lngTable
End With
Range("A:Z").EntireColumn.AutoFit
Set oXML = Nothing
Set oHTML = Nothing


Sheets.Add.Name = stockcode


Worksheets("Sheet1").Select
Worksheets("Sheet1").Cells.Select
Selection.Copy
Worksheets(stockcode).Select
Worksheets(stockcode).Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Sheet1").Cells.Clear


End Sub

 

[pike]

Hi patricklee,
Can you please add code tags to you vba script

does this code capture the last two columns .. Four in total

Option Explicit
Sub Web_Table_Option_One()
Dim xml    As Object
Dim html   As Object
Dim objTable As Object
Dim result As String
Dim lRow As Long
Dim lngTable As Long
Dim lngRow As Long
Dim lngCol As Long
Dim ActRw As Long
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
With xml
.Open "GET", "http://markets.ft.com/research/Markets/Tearsheets/Financials?s=ORCL:NYS&subview=IncomeStatement", False
.send
End With
result = xml.responseText
Set html = CreateObject("htmlfile")
html.body.innerHTML = result
Set objTable = html.getElementsByTagName("table")
 For lngTable = 0 To objTable.Length - 1
        For lngRow = 0 To objTable(lngTable).Rows.Length - 1
            For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
                ThisWorkbook.Sheets("Sheet1").Cells(ActRw + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
            Next lngCol
        Next lngRow
        ActRw = ActRw + objTable(lngTable).Rows.Length + 1
    Next lngTable
End Sub

 

[patricklee]

Hi Pike, it says Access Denied and identified the error at .send

 

[pike]

very strange as i dont have any access problem and the code copies the table to a worksheet

do you run the exact same code as in the post?

 

[patricklee]

As in I used the code you gave me. Should I add it into my preexisting code?

 

[pike]

No just run it to capyure the four columns

your first code runs and copies two columns not the four .. but the code a provided doesnt .. i dont know as i works on my side..