Originally posted on 08/08/2010:

Here is the excel macro VB code to get NFL data from Statfox


'
' Get NFL Date from Statfox -- Enter specified years in the appropriate variable definitions
'

'
On Error Resume Next
Dim Datenum As Integer
Dim Datestart As Integer
Dim sht As String
Dim i As Integer
Dim n As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Datenum = 2009 'This corresponds to the desired starting data that fits the StatFox linking structure
Datestart = 2000
For n = 0 To (Datenum - Datestart)

For i = 1 To Range("NFLteams").Rows.Count 'NFLteams refers to the Range of teams labeled in the workbook
sht = Range("NFLteams").Cells(i, 1).Value
Worksheets.Add().Name = sht & Datenum
Sheets(sht).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;~" & Datenum & "~teamid~" & sht & ".htm", _
Destination:=Range("$A$1"))
.Name = sht & Datenum
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4,7,8"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With Range("A2", Cells(Rows.Count, 1).End(xlUp))
.SpecialCells(xlCellTypeBlanks, xlTextValues).EntireRow.Delete
End With
Next i
Datenum = Datenum - 1 'Count down or Count up from a given date
Next n
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub[/extra]

What you need to do before running is create a table with the names of each team according to the parameters that correspond to the web address, i.e. San Diego = San+Diego, Giants = NY+Giants

You can speed up the process by importing a team report or statistics table from the website and find all '" "' and replace with '+' (Find spaces and replace with +), go here and copy and paste or the more efficient way is to import through excel web service browser


Then select all the teams in the table and define a name to the range, a brief survey of the code and one can see I named the range "NFLTeams"

That's it, run the code. Takes a while but gets the job done. The workbook will contain however many years worth of sheets for each team, all named with the same format, team and year (Arizona2009, Washington2004, NY+Jets2007, etc...)

Each sheet will have the date, opponent, score, line, total, ats results, total results, offense/defense - rushing yds, rypa, passing yards, pypc