Data import into Excel from website

[Kud05]

With help last year I developed a spreadsheet to import tables of data from this football stats website (http://www.soccerbot.com/france/results/fra2010x.htm?Team=All Teams) into excel, the code has worked for the past two years, but now without making any changes the worksheet has stopped working and displays one of the following errors;

Runtime error 13 “Mismatch” on the line highlighted below.

Runtime Error 462 : Remote server machine does not exist or unavailable with the line highlighted below

highlighting the line;

Set oResultPage = ie.Document.

Sub getdata()
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 r.Text
'Check for good connection to web page loop!
Do Until ie.readyState = READYSTATE_COMPLETE
DoEvents
Application.Wait Now() + TimeValue("00:00:01")
Application.SendKeys "~"

Loop
Do Until ie.Busy = False
DoEvents
Loop

Dim oResultPage As HTMLDocument
Dim AllTables As IHTMLElementCollection
Dim xTable As HTMLTable
Dim TblRow As HTMLTableRow
Dim myWkbk As Worksheet

Sheets(2).Select
Cells.Delete
'copy "data" table
Set oResultPage = ie.Document
Set AllTables = oResultPage.getElementsByTagName("table")
Set xTable = AllTables.Item(0)
Set myWkbk = ActiveWorkbook.Sheets("InputData")
For Each TblRow In xTable.Rows
    k = k + 1
    For Each tblCell In TblRow.Cells
        c = c + 1
        myWkbk.Cells(k, c) = tblCell.innerText
    Next tblCell
    c = 0
Next TblRow
k = 0
ie.Quit

End Sub

I have uploaded the worksheet I use.

http://www.filefactory.com/file/b3010g9/n/Goal_Times1.xls

Not being very good at Excel code can anybody help to fix the workbook to get it working again?

Thanks

 

[Norie]

It's probably not really a problem with the code, more likely it's that the website has changed.

That's one of the things you'll face when trying to do this sort of thing.

It could even be something to do with your current internet connection/access/etc.

This worked for me, it's basically the code you posted with some minor changes.

Option Explicit
Sub getdata()
Dim oResultPage As HTMLDocument
Dim AllTables As IHTMLElementCollection
Dim xTable As HTMLTable
Dim TblRow As HTMLTableRow
Dim TblCell As HTMLTableCell
Dim myWkbk As Worksheet
Dim ie As InternetExplorer
'Dim i As Range
Dim c As Long
Dim k As Long
    Sheets("InputData").Cells.ClearContents
    Set ie = CreateObject("InternetExplorer.Application")
 
    ie.Visible = True
    'Go to this Web Page!
    ie.navigate "http://www.soccerbot.com/france/results/fra2010x.htm?Team=All Teams"
    'Check for good connection to web page loop!
    Do Until ie.readyState = READYSTATE_COMPLETE: DoEvents: Loop
    Do Until ie.Busy = False: DoEvents: Loop
 
    Set oResultPage = ie.Document
 
' get data from all tables on the web page
 
    Set AllTables = oResultPage.getElementsByTagName("table")
 
    Set xTable = AllTables.Item(0)
 
    Set myWkbk = ActiveWorkbook.Sheets("InputData")
 
    For Each TblRow In xTable.Rows
        k = k + 1
        For Each TblCell In TblRow.Cells
            c = c + 1
            myWkbk.Cells(k, c) = TblCell.innerText
        Next TblCell
        c = 0
    Next TblRow
 
    ie.Quit
 
End Sub

None of the changes I made concerned the error(s) you were getting and it works fine for me.

The only major change was to use the URL from your post.

PS I tried the original code before changing it and it worked too.

Oh, and what data is it you want to get anyway?

PPS The link to the download caused my browser to break, twice.

 

[Kud05]

Thanks for the comments.

Firstly apologies about the link to the download!

I used the data regarding the goalscorer and the minute the goal is scored in to set up a database of first goalscorers for betting purposes. Using this website I can get the data for all the major leagues in Europe.

Back to the 'problem' I copied the worksheet and used it on another machine and it did indeed work. What specific things may cause it not to work on my machine? Internet settings, version of excel?

Thanks again

 

[Norie]

Those are some of the reasons, perhaps even the most common, and it's pretty hard to pinpoint them never mind deal with them.

Just recently somebody wanted help getting French house prices or something like that.

I'm not in France but the code worked fine for me (most of the time anyway) but not for the OP.

Go figure!!!