Copy webdata into excel

[carlleese24]

hi


Could someone help me please

from this address
http://www.xscores.com/LiveScore.do...te=eventFS&sport=1&newState=promptSoccerTable

I am trying to create a macro so it will copy the table from the above website address above into excel. I have tried to import the data but it won't work. Is there another way using a VBA code to get that table into excel automatically.


I would really appreciate it if someone could help me on this problem

Carl

 

[George J]

Goto Data - import external data - new web query

enter the url that you need and select the table you require (yellow arrow becomes green tick)

The data will then be transferred and i believe there is an option to refresh the data every minute.

 

[carlleese24]

hi

I Followed your instructions but it doesn't work, it comes with this message

unable to open
http://www.xscores.com/LiveScore.do...te=eventFS&sport=1&newState=promptSoccerTable
the internet site reports that a connection was established but the data is not available

so is there another of copying this data to excel through a macro automatically

 

[Norie]

carlleese24

What data do you want from the page?

I was able to use the first link you posted via Data>Get External Data...New Web Query...
and it worked.

 

[carlleese24]

hi

just the football league table the first bit goes okay but then when I try to put it in excel it produces that error

 

[Norie]

carlleese24

I don't think that's an Excel error, perhaps it's something to do with your connection?

 

[carlleese24]

hi

No its not my internet connection because I have tried to import other webdata to my spreadsheet with success

 

[George J]

If web queries don't work for this, how about this code? It'll put the table onto Sheet2 and refresh every 15 min until "STOP" is entered into A1.

(Open excel, press ALT+F11 - to open the VB Editor - Goto insert, module and paste the code in the module.)

goto tools, references and "tick" Microsoft Internet Controls and Microsoft HTML Object Library - then you can run this code.

Sub xScoresTable_Import()

Dim ie As InternetExplorer
Dim i As Range
Dim x As Range
Dim y As Range
Dim BinString As String
 
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
'Go to this Web Page!
ie.navigate "http://www.xscores.com/LiveScore.do?state=promptSoccerTable&state=eventFS&sport=1&newState=promptSoccerTable "
'Check for good connection to web page loop!
Do Until ie.readyState = READYSTATE_COMPLETE
DoEvents
Loop
Do Until ie.Busy = False
DoEvents
Loop
' type STOP in cell A1 to stop the macro/refresh
1
If Range("A1").Value = "STOP" Then Exit Sub
Cells.Select
Selection.Clear '.Delete
Range("A1").Select
Dim oResultPage As HTMLDocument
Dim AllTables As IHTMLElementCollection
Dim xTable As HTMLTable
Dim TblRow As HTMLTableRow
Dim myWkbk As Worksheet
 
'copy "data" table
Set oResultPage = ie.Document
Set AllTables = oResultPage.getElementsByTagName("table")
Set xTable = AllTables.Item(2)
Set myWkbk = ActiveWorkbook.Sheets("Sheet2")
For Each TblRow In xTable.Rows
    r = r + 1
    For Each tblCell In TblRow.Cells
        c = c + 1
        myWkbk.Cells(r, c) = tblCell.innerText
    Next tblCell
    c = 0
Next TblRow
r = 0
' refresh values every 15 mins
s = Now
Do Until Now >= s + TimeValue("00:15:00")
DoEvents
Loop
GoTo 1
End Sub

Does this do what you need?

 

[carlleese24]

hi George J

This code is exactly what I need thank you very much. I have been trying to figure out how this code works but I am not sure could you tell me please.

Does the macro get inside the source code and extracts certain information from there

 

[kyrgan]

carlleese24

What I recommend you do is use the F8 key to step through the macro line by line to watch what happens. While in the macro editor you can hover your mouse over variables to see what their current values are.

I hope this is helpful.