Sub TestingWebQueryImport()
'
' TestingWebQueryImport Macro
'
'
Sheets.Add After:=ActiveSheet
ActiveWorkbook.Queries.Add name:="Defense Table", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""http://www.pro-football-reference.com/boxscores/201610060sfo.htm""))," & Chr(13) & "" & Chr(10) & " Data27 = Source{27}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data27,{{""Player"", type text}, {""Tm"", type text}, {""Def Interceptions Int"", type text}, {""Def Interceptions Yds"", type text}, {""Def Interceptions TD"", type text}," & _
" {""Def Interceptions Lng"", type text}, {""Sacks & Tackles Sk"", type text}, {""Sacks & Tackles Tkl"", type text}, {""Sacks & Tackles Ast"", type text}, {""Fumbles FR"", type text}, {""Fumbles Yds"", type text}, {""Fumbles TD"", type text}, {""Fumbles FF"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Defense Table""" _
, destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Defense Table]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Defense_Table"
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Queries.Add name:="Passing, Rushing, & Receiving Table", _
Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""http://www.pro-football-reference.com/boxscores/201610060sfo.htm""))," & Chr(13) & "" & Chr(10) & " Data25 = Source{25}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data25,{{""Player"", type text}, {""Tm"", type text}, {""Passing Cmp"", type text}, {""Passing Att"", type text}, {""Passing Yds"", type text}, {""Passing TD"", type text}," & _
" {""Passing Int"", type text}, {""Passing Sk"", type text}, {""Passing Yds2"", type text}, {""Passing Lng"", type text}, {""Passing Rate"", type text}, {""Rushing Att"", type text}, {""Rushing Yds"", type text}, {""Rushing TD"", type text}, {""Rushing Lng"", type text}, {""Receiving Tgt"", type text}, {""Receiving Rec"", type text}, {""Receiving Yds"", type text}, {" & _
"""Receiving TD"", type text}, {""Receiving Lng"", type text}, {""Fumbles Fmb"", type text}, {""Fumbles FL"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Passing, Rushing, & Receiving Table""" _
, destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Passing, Rushing, & Receiving Table]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Passing__Rushing____Receiving_Table"
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Queries.Add name:="Scoring Table", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""http://www.pro-football-reference.com/boxscores/201610060sfo.htm""))," & Chr(13) & "" & Chr(10) & " Data16 = Source{16}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data16,{{""Quarter"", Int64.Type}, {""Time"", type time}, {""Tm"", type text}, {""Detail"", type text}, {""ARI"", Int64.Type}, {""SFO"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Scoring Table""" _
, destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Scoring Table]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Scoring_Table"
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Queries.Add name:="Team Stats Table", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""http://www.pro-football-reference.com/boxscores/201610060sfo.htm""))," & Chr(13) & "" & Chr(10) & " Data23 = Source{23}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data23,{{"""", type text}, {""ARI"", type text}, {""SFO"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Team Stats Table""" _
, destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Team Stats Table]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Team_Stats_Table"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("I14").Select
End Sub
I didn't like how heavy this method was. It seems to be adding each individual query to the workbook, and then adding 3 worksheets for each table after doing a select all to extract each tables data. Is it possible to do this any cleaner or smoother?
Bookmarks