MBL Players Batting statistics

The following VBA code will retrieve data from a web query of sports.yahoo and create a spreadsheet with the Major league Baseball player's batting statistics.

The two functions (GET_NUMROWS and GET_NUMCOLS) used by the subroutine must be copied from the Number Rows Columns document.. to run this code.

SAS Fifth Dimension Macro-Input Output

Review the following code, copy it to Excel and execute it.

Sub MLB_PLAYER_BATTING1()
    Dim players(132, 1000)
    Dim Team_ID(30)
    SUBNAME = "MLB_PLAYER_BATTING"
   'These URLs were copy from the website sports.yahoo.com/mlb for each team
    Team_ID(1) = "ari": Team_ID(2) = "atl": Team_ID(3) = "bal": Team_ID(4) = "bos"
    Team_ID(5) = "chc": Team_ID(6) = "chw": Team_ID(7) = "cin": Team_ID(8) = "cle"
    Team_ID(9) = "col": Team_ID(10) = "det": Team_ID(11) = "fla": Team_ID(12) = "hou"
    Team_ID(13) = "kan": Team_ID(14) = "laa": Team_ID(15) = "lad": Team_ID(16) = "mil"
    Team_ID(17) = "min": Team_ID(18) = "nym": Team_ID(19) = "nyy": Team_ID(20) = "oak"
    Team_ID(21) = "phi": Team_ID(22) = "pit": Team_ID(23) = "sdg": Team_ID(24) = "sfo"
    Team_ID(25) = "sea": Team_ID(26) = "stl": Team_ID(27) = "tam": Team_ID(28) = "tex"
    Team_ID(29) = "tor": Team_ID(30) = "was":
    POSITIONS = "Batting"
    NXT = 0
    NUM = 0
    For KK = 1 To 3 'TEAM_STATS_SIZE
        SPORT_URL = "URL;http://sports.yahoo.com/mlb/teams/" & Team_ID(KK) & _
                    "/stats?stat_category=mlb.stat_category.1"
        NXT = NXT + 1
       'Open temp book and temp sheet to save the statistics
        If NXT = 1 Then
           Workbooks.Add
           SHEET_NAME = ActiveSheet.Name
           Sheets(SHEET_NAME).Name = POSITIONS
        End If
        Sheets.Add After:=Sheets(Sheets.Count)
        SHEET_NAME = ActiveSheet.Name
        Sheets(SHEET_NAME).Select
        SHEET_NAME = ActiveSheet.Name
        Sheets(SHEET_NAME).Name = UCase(Team_ID(KK)) '& "_" & POSITIONS
       'Download website to worksheet
        With ActiveSheet.QueryTables.Add(Connection:=SPORT_URL, _
           Destination:=Range("$A$1"))
          .Name = "mlb" & " players"
          .RefreshStyle = xlInsertDeleteCells
          .RefreshPeriod = 0
          .WebFormatting = xlWebFormattingNone
          .WebDisableDateRecognition = True
          .WebSelectionType = xlSpecifiedTables
          .WebTables = "1,1"
          .BackgroundQuery = False
          .Refresh
        End With
       'Include team ID for each player
        Columns("A").Select
        Selection.Insert Shift:=xlToRight
        Cells(1, 2).Select
        NUMROWS = GET_NUMROWS
        For II = 1 To NUMROWS
            Cells(II, 1) = UCase(Team_ID(KK))
        Next
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        Cells(1, 1) = "Team ID"
       'Copy team stats to array variable players
        Cells(1, 1).Select
        NUMCOLS = GET_NUMCOLS
        Cells(1, 1).Select
        NUMROWS = GET_NUMROWS
        For II = 1 To NUMROWS
            For JJ = 1 To NUMCOLS
                players(II, JJ) = Cells(II, JJ)
            Next
        Next
       'Delete the teams spreadsheet
        Application.DisplayAlerts = False
        ActiveWindow.SelectedSheets.Delete
        Application.DisplayAlerts = True
       'Join the teams into one sheet
        Sheets(POSITIONS).Select
        If NUM = 0 Then
           NUM = NUM + 1
           II = 1
           For JJ = 1 To NUMCOLS
               Cells(NUM, JJ) = players(II, JJ)
           Next
        End If
        For II = 4 To NUMROWS
            NUM = NUM + 1
            For JJ = 1 To NUMCOLS
                Cells(NUM, JJ) = players(II, JJ)
            Next
        Next
        Cells.EntireColumn.AutoFit
    Next
    Cells(1, 1).Select
    ActiveWorkbook.Saved = True
End Sub