I'm trying to figure out a way to query data for batters vs left hand and batters vs right hand. The problem is, it only lists 40 per page and I don't know how to query all the pages onto a single page in excel. This is what I'm referring to: http://espn.go.com/mlb/stats/batting/_/ ... fied/false. Any help would be greatly appreciated.
This is the query I came up with - it cycles through but no data actually appears:
- Code: Select all
Sub BatterVsLeftHand()
Dim nextRow As Integer, n As Integer
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
For n = 1 To 441 Step 40
Application.StatusBar = "Processing Page " & n
nextRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://espn.go.com/mlb/stats/batting/_/split/31/count/" & n & "/qualified/false", _
Destination:=Range("A" & nextRow))
.Name = "mlb/stats/batting/_/split/31/count/440/qualified/false"
.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 = "22"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ThisWorkbook.Save
Next n
Application.StatusBar = False
End Sub