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.

 

[VBA Geek]

Sub Yahoo()
    Dim aBrowser As Object
    Set aBrowser = CreateObject("InternetExplorer.Application")
    Dim Coll As Object, HT As Object
    Dim r As Long, c As Long
    
    With aBrowser
        .Silent = True
        .Visible = False
        .Navigate "http://ca.sports.yahoo.com/nhl/stats/byposition?pos=C,RW,LW,D"
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
    End With
    
    Set HT = aBrowser.Document
    Set Coll = HT.getElementsByTagName("table")
    
    Application.Calculation = xlCalculationManual
    For r = 0 To Coll(4).Rows.Length - 1
        For c = 0 To Coll(4).Rows(r).Cells.Length - 1
            Cells(r + 1, c + 1) = Coll(4).Rows(r).Cells(c).innerText
        Next
    Next
    Application.Calculation = xlCalculationAutomatic
    
    Set aBrowser = Nothing: Set HT = Nothing: Set Coll = Nothing
End Sub

 

[Bucko3030]

how to i enter that code

 

[VBA Geek]

File > Options >Cuatomize Ribbon, on the right check "Developer" then press ok. You'll see a new tab on your excel called "Developer".

go to it, click insert, insert module, paste the above code in it then move your cursor somewhere in the mid of the code and press F5

 

[Bucko3030]

do you know why iam getting script errors now is it because yahoo update there web site? I put you code in it does import the data but my other formula doesn't work what happens is I have sheets one for each owner of a team one for the players stats that comes in from yahoo and one for goalie stats also from yahoo this is the formula I use on each owner sheet to pull data to each player =IF(ISNUMBER(MATCH($D5, Players!$A$1:$A$20,0)),INDEX(Players!NHL_2010_skaters, MATCH($D5, Players!$A$1:$A$20,0), MATCH(E$4, Players!$A$1:$CE$1, 0)),0) I also tried different web sites and I get script errors there also. Iam running excel 2007 I wonder if I upgrade to 2013 if it would fix my problem

 

[Bucko3030]

anyone know a way around my script error?

 

[Bucko3030]

on my old computer I used to right click and export to excel but now on my new windows 7 system I can't do this. because if I can't get web query to work I was just thinking I can export to excel then copy and past it to my other excel sheet

 

[VBA Geek]

the code will import you data, you need to adapt your formula to the new scenario, it should only be a matter of changing formula references

 

[Bucko3030]

I have tried for over 3hours can't get it to work only thing that worked for me is using that code in a new excel let it import the data then copy and past the data in my players sheet in my hockey excel sheet and it works not sure why it works that way but not when I put the code in the hockey excel sheet I just get #### in all the fields. I know its the formula that is wrong but iam not very good at excel

 

[Bucko3030]

is there anyway I can send the file to you can you look at it and see what iam doing wrong?