Importing web query from web script error

Bucko3030

New Member
Joined
Feb 1, 2014
Messages
18
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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Code:
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
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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

New Member
Joined
Feb 1, 2014
Messages
18

ADVERTISEMENT

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
 
Last edited:

Bucko3030

New Member
Joined
Feb 1, 2014
Messages
18

ADVERTISEMENT

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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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

New Member
Joined
Feb 1, 2014
Messages
18
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

New Member
Joined
Feb 1, 2014
Messages
18
is there anyway I can send the file to you can you look at it and see what iam doing wrong?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top