Importing web query from web script error

[Bucko3030]

Hi I have been using a web query to imports data for my hockey pool. I been importing it for about 2 years now from yahoo sports everything was fine. Untill yesterday yahoo must have change something now I can't import the data any more I go check my connection I get script error line 18512 char 1 error 'Y' is undefined code 0 here is the webpage i use NHL - Statistics by Position - Yahoo Canada Sports it was easy to import before i would just put the yellow arrow next the players name and save now i can't put the arrow by the players now i can only import the whole web page but then my code don't work.

 

[Bucko3030]

they code they helped with me here didn't work but I only had 3 weeks left in the season so I just did the stats by hand. New season starts in 2 weeks so I will be working on the code again just waiting to see if yahoo will change there web page again

 

[sportshelp]

Unfortunately it doesn't. I get an error when inputting that code. When I go to Debug, it points to ".send"

does this work for you?

Sub imp_NFL()
Const URL = "http://sports.yahoo.com/nfl/stats/byteam?group=Offense&cat=Passing&conference=NFL&year=season_2014&sort=42&old_category=Passing&old_group=Offense"
Dim oXML As Object, oHTML As Object, x As Long, y As Long
Set oXML = CreateObject("MSXML2.XMLHTTP")
Set oHTML = CreateObject("HTMLFILE")
With oXML
    .Open "GET", URL, False
    .send
    oHTML.body.innerHTML = .responseText
End With
With oHTML.getElementsByTagName("Table").Item(6)
    For x = 0 To .Rows.Length - 1
        For y = 0 To .Rows(x).Cells.Length - 1
            Range("A1").Offset(x, y).Value = .Rows(x).Cells(y).innerText
        Next
    Next x
End With
MsgBox "Done", vbOKOnly
Set oXML = Nothing
Set oHTML = Nothing
End Sub

 

[Bucko3030]

ok I was trying to get my import to work again this weekend but I don't know how many hours I spent trying to import for web,, playing with the code still can't get it to import from excel. all I want to do is import the data on this page to my excel page
https://ca.sports.yahoo.com/nhl/stats/byposition?pos=C,RW,LW,D

Used to be so easy click import select web put the yellow mark on what you wanted and then done now I get errors. can anyone help me here is the code I tried or is there any other way to import the data from the web page

Sub Bucko3030()
'
' Bucko3030 Macro
'
    Dim aBrowser As Object
    Dim htm As Object
    Dim elemCollection As Object
 Dim URLPArray
 Dim ShtArray
 Dim MyArray
 Dim d As Long, r As Long, c As Long
 Dim i As Integer
      
 URLPArray = Array("C,RW,LW,D", "G&conference=NHL&year=season_2013&qualified=1")
 ShtArray = Array("Players", "Goalies")
 Application.Calculation = xlCalculationManual
For i = 0 To 1
    Sheets(ShtArray(i)).Select
    Range("A1").Select
    
    Set htm = CreateObject("htmlFile")
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "[URL]https://ca.sports.yahoo.com/nhl/stats/byposition?pos=C,RW,LW,D[/URL]" & URLPArray(i) & "", False
        .Send
        htm.body.innerhtml = .responsetext
    End With
    Set elemCollection = htm.getElementsBytagName("table")(4)
    
    For r = 0 To elemCollection.Rows.Length - 1
        ReDim MyArray(elemCollection.Rows(0).Cells.Length - 1)
        d = 0
        For c = 0 To elemCollection.Rows(r).Cells.Length - 1
            If c > 2 And elemCollection.Rows(r).Cells(c).innertext = Chr$(32) Then GoTo Nxtc
                MyArray(d) = elemCollection.Rows(r).Cells(c).innertext
                d = d + 1
Nxtc:
        Next c
        
        Cells(r + 1, 1).Resize(, elemCollection.Rows(0).Cells.Length) = MyArray
Nxtr:
    Next r
Set htm = Nothing
Next i
Application.Calculation = xlCalculationAutomatic
'
End Sub

 

[ukmikeb]

Hi

I don't understand why an error is caused using the CreateObject("msxml2.xmlhttp") version.

However, I have made a modification to use CreateObject("InternetExplorer.Application"), similar to @VBAGeek, and a further modification to the creation of the URL which should help when changing seasons.

Sub Bucko3030()
'
' Bucko3030 Macro
'
 Dim aBrowser As Object
 Dim elemCollection As Object, htm As Object
 Dim d As Long, r As Long, c As Long
 Dim MyArray, myURL
    
 Dim URLPArray
 Dim ShtArray
 
 URLPArray = Array("C,RW,LW,D", "G")
 ShtArray = Array("Players", "Goalies")
 Application.Calculation = xlCalculationManual
 For i = 0 To 1
    Sheets(ShtArray(i)).Select
    
    If i = 0 Then
        Range("NHL2010_Players").Offset(2).ClearContents
    Else
        Range("NHL2010_Goalies").Offset(2).ClearContents
    End If
 
    myURL = "http://ca.sports.yahoo.com/nhl/stats/byposition?pos=" & URLPArray(i) & "&conference=NHL&year=season_2014&qualified=1"
    
    Set aBrowser = CreateObject("InternetExplorer.Application")
    With aBrowser
        .Silent = True
        .Visible = False
        .Navigate myURL
        While .Busy: DoEvents: Wend
        While .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
    End With
    
    Set htm = aBrowser.Document

    Set elemCollection = htm.getElementsBytagName("table")(4)
    
    For r = 0 To elemCollection.Rows.Length - 1
        ReDim MyArray(elemCollection.Rows(0).Cells.Length - 1)
        d = 0
        For c = 0 To elemCollection.Rows(r).Cells.Length - 1
            If c > 2 And elemCollection.Rows(r).Cells(c).innertext = Chr$(32) Then GoTo Nxtc
                MyArray(d) = elemCollection.Rows(r).Cells(c).innertext
                d = d + 1
Nxtc:
        Next c
        
        Cells(r + 1, 1).Resize(, elemCollection.Rows(0).Cells.Length) = MyArray
Nxtr:
    Next r

    Set aBrowser = Nothing
    Set htm = Nothing
    Set elemCollection = Nothing
Next i

Application.Calculation = xlCalculationAutomatic
'
End Sub

The code has been tested and creates the desired results.

@sportshelp This code should work for you also.

hth

 

[Bucko3030]

Thanks for the help I tried code but I get run time error 91 object or with block variable not set

For r = 0 To elemCollection.Rows.Length - 1

 

[ukmikeb]

Hi

That was an odd one as it worked for me ok this morning and it errored with same code as you this evening.

Change the myURL assignment as follows :-

    myURL = "http://ca.sports.yahoo.com/nhl/stats/byposition?pos=" & URLPArray(i) & _
            "&conference=NHL&year=season_2014&qualified=1" & ""

Good luck.