VBA Runtime Error 91 Object Variable Not Set - What am I doing wrong?

Question

I am trying to get data from internet to Excel spreadsheet. Namely, I want the names of the AP Top 25 NCAAF teams from past 12 years, for all 17 weeks of the season (including final rankings). My code is based off this tutorial: https://www.youtube.com/watch?v=7sZRcaaAVbg

I'm getting the error at the line:

SchoolNames(iYear, iWeek, iRank) = Trim(Doc.getElementsByTagName("school")(iRank - 1).innerText)

Here's my code:

---

Option Explicit

Sub Data_Collection()

Application.ScreenUpdating = False

Dim iYear                   As Integer
Dim iWeek                   As Integer
Dim IE                      As New InternetExplorer
Dim sYear                   As String
Dim sWeek                   As String
Dim SchoolNames(12, 17, 25) As String
Dim Doc                     As HTMLDocument
Dim iRank                   As Integer


For iYear = 2002 To 2013

sYear = CStr(iYear)

    For iWeek = 1 To 17

    If iWeek = 17 Then
        sWeek = "1/seasontype/3"
    Else
        sWeek = CStr(iWeek)
    End If

    IE.Visible = False
    IE.navigate "http://espn.go.com/college-football/rankings/_/poll/1/year/" & sYear & "/week/" & sWeek

    Do
        DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE

    Set Doc = IE.Document

    For iRank = 1 To 25
        SchoolNames(iYear, iWeek, iRank) = Trim(Doc.getElementsByTagName("school")(iRank - 1).innerText)
    Next iRank

    Next iWeek
Next iYear


End Sub

---

Additionally, I'm not sure even after I fix the Runtime 91 error that I am passing the right stuff into the .getElementsByTagName(). Any suggestions?

Answer 1

You are close but need to make a couple small modifications.

1. SchoolNames(iYear,... iYear variable is a four digit year but you are setting an array so it needs to be the location in the array, not the year)
2. The element in the webpage that has the school information is labeled by classname not TagName. Change to Doc.getElementsByClassName
3. Make sure you quite IE so it's not running in the background IE.Quit

Example code for returning Name of school that ranked #1 in 2002 week 1:

Sub getSchoolName()
   Dim IE As New InternetExplorer

   IE.Visible = False
   IE.navigate "http://espn.go.com/college-football/rankings/_/poll/1/year/2002/week/1"

   Do
      DoEvents
   Loop Until IE.readyState = READYSTATE_COMPLETE

   Set Doc = IE.Document

   Dim schoolName As String
   schoolName = Trim(Doc.getElementsByClassName("school")(0).innerText)
   IE.Quit

   MsgBox (schoolName) 
End Sub

---

Results look like this:

---

• Make sure you include references to in your project.

---

Here is one way to reset the year as an array position:

sYearCntr = iYear - 2002
SchoolNames(sYearCntr, iWeek, iRank) = ....