Importing Data From Web Page

[edneco]

Hello again..... I need to extract 3 tables from web, but I can´t do it.... I´m using a VBA code to import some tables from web, by recording macros... This is the only way that I can "write" some code....

This is it: w w w.stat-football.c o m/en/t/eng10.php?c=25&ht=h1

As you can see, the table is "1st half total goals 1.5", Home and Away. But I need the "1st half total goals 0.5" table, Home and Away.

But the problem is the 3 tables that I want to import, needed to be selected by choose them and clicking in a submit button. But with a macro, this steps won´t appear in the code....

Then I ask for help, I this guy that help me, made a code to me, but don´t work..... Can you guys help me with this???



Public HTMLdoc As Object
Public PageSrc As String

Function GetText(ByRef objHTML As Object, ByRef TextOut As String)

Dim flag As Boolean
Dim i As Long

' Returns the text from HTML elements using recursion.
If objHTML.HasChildNodes = True Then

For i = 0 To objHTML.ChildNodes.Length - 1
With objHTML.ChildNodes(i)
If UCase(.nodeName) = "BR" Then flag = True

Select Case .NodeType
Case 1 'Element
DoEvents

' Check if element contains any nodes with text.
If .HasChildNodes Then
Call GetText(objHTML.ChildNodes(i), TextOut)
End If

Case 3 'Text
If .NodeValue <> "" Then
If flag = True Then TextOut = TextOut & vbLf
TextOut = TextOut & .NodeValue
End If
End Select
End With

flag = False
Next i

End If

' Return a text string of the elements' text separated by a pipe character | .
GetText = TextOut

End Function

Sub OpenWebPage(ByVal URL As String)

PageSrc = ""

With CreateObject("MSXML2.XMLHTTP")
.Open "GET", URL, True
.Send

While .readyState <> 4: DoEvents: Wend

' Check for any connection errors.
If .statusText <> "OK" Then
MsgBox "ERROR: " & .Status & " - " & .statusText, vbExclamation
Exit Sub
End If

PageSrc = .ResponseText
End With

' Create an empty HTML Document and load it with the Page Source.
Set HTMLdoc = CreateObject("htmlfile")
HTMLdoc.body.innerHTML = PageSrc

End Sub

Sub ListGameData()

Dim oButton As Object
Dim oDiv As Object
Dim oHeader As Object
Dim oItem As Object
Dim oRow As Object
Dim oSelect As Object
Dim oTable As Object
Dim Rng As Range
Dim Text As String
Dim Wks As Worksheet


Set Wks = Folha2
Set Rng = Wks.Range("E5:Q25, S5:AE25, AG5:AS25")

OpenWebPage "w w w.stat-football. c om /en/t/eng10.php?c=25&ht=h1"

For Each oItem In HTMLdoc.getElementsByTagNAme("select")
If oItem.Name = "tot" Then
Set oSelect = oItem
Exit For
End If
Next oItem

For Each oItem In HTMLdoc.getElementsByTagNAme("input")
If oItem.Type = "submit" And oItem.Value = " >> " Then
Set oButton = oItem
Exit For
End If
Next oItem

Set oDiv = HTMLdoc.getElementById("y0")
Set oHeader = oDiv.ChildNodes(0)
Set oTable = HTMLdoc.getElementById("tb01")


For n = 1 To 3
oSelect.selectedIndex = n - 1
oButton.Click ' <--- This causes an error

For r = 0 To oTable.Rows.Length - 1
For c = 0 To oTable.Rows(r).Cells.Length - 1
Text = ""
Rng.Areas(n).Item(r + 1, c + 1) = GetText(oTable.Rows(r).Cells(c), Text)
Next c
Next r

Rng.Areas(n).Columns.AutoFit
Next n

End Sub



I thank you, in advance, if you help me with this.....

Best regards, Eduardo

[SamT]

That is a pretty generic Thread Title.

I think I will change it

[edneco]

Thank you SamT.... In fact that title is used many times......

Bellow in your post you said "Can you explain what are you trying to do, and not how you think you want to do it?"

Following this sentence, what I want is to extract the data from 3 tables in that link. By default when we open that link, the page shows the "1st half total goals 1.5", Home and Away.

But the 3 tables that I really need are "1st half total goals 0.5", Home and Away......

Need help to resolve the problem in the code above, or a new one that works........

Can anyone help with this issue??? I really need this tables because I trying to do some research, to do a work for graduation.

Again, thank you very much for any help you can give......

[SamT]

Link = http://www.stat-football.com/en/t/eng10.php?c=25&ht=h1

Source code analysis:

Tables (JS Link Names in Bold, active Table captions in Italic):
OverAll 1st half total goals 1.5
<table class="tar" id="tb01" style="border:1px solid #88c3c3;" cellpadding="1">
Home home 1HT total goals 1.5
<table class="tar" id="tb02" style="border:1px solid #88c3c3;" cellpadding="1">
Away away 1HT total goals 1.5
<table class="tar" id="tb03" style="border:1px solid #88c3c3;" cellpadding="1">

All three tables re available in the source code at the same time, Unfortunately they are complex layers of un-ID'ed tables

I looked at another data table page from that site and they are not all in the same format.

I am attaching a text file with the html code of those three tables.(3.4KB. Unzips to 66kb) I am only scanning the source code of that page, not doing any in-depth reading, so I may have more or less than all the code involved.

I suggest that you create a visual of one of them showing the "physical" layout, the class and id of each table. be sure to somehow differentiate td's and enclosed tables.

Without knowledge of Data Structure, programming is impossible.

[edneco]

SamT thank you very much for your time to see this problem.....

But unfortunately I can´t help you what you asking for.... Like I said, the only code that I "wrote" is recording macros in excel...

I´m too noob with VBA..... Sorry.....

If you or anyone else can do this for me, I thank you very much......