Wonder if someone could help me with the code below. First, it does work however so you can try it. Its not very efficient in the following manner. I am using it to generate information for all NCAA coaches. I have completed the first 2 however you can imagine doing this for all of them will be very uneconomical. I would like to ask for a couple of things..
1) for the wins, losses and experience paramters (the games played will likely come out) I am having problems with creating a search function that will sum the info from row 3 down until the End but then come up 2 rows..sort of like End(xlDown).offset(-2,0).
2) Because I am still new, I can look at the code and conceptualize that there must be a way to loop this rather than rewriting essentially the same code out dozens of times. The main difference will come in 2 places really. The first will be the html page as each coach has his own...so I imagine writing a string variable for the coach name (as the rest of the html is the same for all) and also in the wins, losses and experience, each coach due to his experience will have more or less rows (hence my hope to have VBA go to the end of the data and then backup 2 rows since not everyone will have just 4 years of experience..Please note that I do not want to include 2017-18 since the season is not over.
Right now I am doing the sub then calling the next one for the next coach. Thats how I would write this, but I hope that someone can make the adjustment keeping in mind that the code also creates a sheet in the workbook named after the coach.
How could this be written to loop the first sub and grab the data for other coaches? Would I just change the name of the coach in the html to a string variable and then list all of the coach names? Im getting better with straight codes but looping is still a challenge given my background has nothing to do with tech.
Thanks for any and all help in advance.
Option Explicit
Public qt As QueryTable
Public ws As Worksheet
Public URL As String
Sub CoachAbileneChristian()
'School: Abilene Christian
'Coach: Joe Golding
URL = "https://www.sports-reference.com/cbb/coaches/joe-golding-1.html"
Set ws = ActiveSheet
'set up a table import (the URL; tells Excel that this query comes from a website)
Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
'tell Excel to refresh the query whenever you open the file
qt.RefreshOnFileOpen = True
'giving the query a name can help you refer to it later
qt.Name = "JoeGoldingCareer"
'you want to import column headers
qt.FieldNames = True
'need to know name or number of table to bring in
'(we'll bring in the first table)
qt.WebFormatting = xlWebFormattingNone
'qt.WebSelectionType = xlAllTables
'qt.WebTables =1
'import the data
qt.Refresh BackgroundQuery:=False
ActiveSheet.Name = "JoeGolding"
'Dim TotalGames As Range
'Set TotalGames = Range("D3:D6")
'Results = WorksheetFunction.Sum(TotalGames)
'Range("P3") = Results
Dim Results As Long
Dim TotalExperience As Range
Dim Career As Integer
Set TotalExperience = Range("B3:B6")
Results = TotalExperience.EntireRow.Count
Range("P3") = Results
Dim TotalWins As Range
Set TotalWins = Range("E3:E6")
Results = WorksheetFunction.Sum(TotalWins)
Range("Q3") = Results
Dim TotalLosses As Range
Set TotalLosses = Range("F3:F6")
Results = WorksheetFunction.Sum(TotalLosses)
Range("R3") = Results
Set ws = Sheets.Add
Sheets(1).Activate
Call CoachAirForce
End Sub
Sub CoachAirForce()
'School: Air Force
'Coach: Dave Pilipovich
URL = "https://www.sports-reference.com/cbb/coaches/dave-pilopovich-1.html"
Set ws = ActiveSheet
'set up a table import (the URL; tells Excel that this query comes from a website)
Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
'tell Excel to refresh the query whenever you open the file
qt.RefreshOnFileOpen = True
'giving the query a name can help you refer to it later
qt.Name = "DavePilipovichCareer"
'you want to import column headers
qt.FieldNames = True
'need to know name or number of table to bring in
'(we'll bring in the first table)
qt.WebFormatting = xlWebFormattingNone
'qt.WebSelectionType = xlAllTables
'qt.WebTables =1
'import the data
qt.Refresh BackgroundQuery:=False
ActiveSheet.Name = "DavePilipovich"
'Dim TotalGames As Range
'Set TotalGames = Range("D3:D6")
'Results = WorksheetFunction.Sum(TotalGames)
'Range("P3") = Results
Dim Results As Long
Dim TotalExperience As Range
Dim Career As Integer
Set TotalExperience = Range("B3:B6")
Results = TotalExperience.EntireRow.Count
Range("P3") = Results
Dim TotalWins As Range
Set TotalWins = Range("E3:E6")
Results = WorksheetFunction.Sum(TotalWins)
Range("Q3") = Results
Dim TotalLosses As Range
Set TotalLosses = Range("F3:F6")
Results = WorksheetFunction.Sum(TotalLosses)
Range("R3") = Results
Set ws = Sheets.Add
Sheets(1).Activate
End Sub
1) for the wins, losses and experience paramters (the games played will likely come out) I am having problems with creating a search function that will sum the info from row 3 down until the End but then come up 2 rows..sort of like End(xlDown).offset(-2,0).
2) Because I am still new, I can look at the code and conceptualize that there must be a way to loop this rather than rewriting essentially the same code out dozens of times. The main difference will come in 2 places really. The first will be the html page as each coach has his own...so I imagine writing a string variable for the coach name (as the rest of the html is the same for all) and also in the wins, losses and experience, each coach due to his experience will have more or less rows (hence my hope to have VBA go to the end of the data and then backup 2 rows since not everyone will have just 4 years of experience..Please note that I do not want to include 2017-18 since the season is not over.
Right now I am doing the sub then calling the next one for the next coach. Thats how I would write this, but I hope that someone can make the adjustment keeping in mind that the code also creates a sheet in the workbook named after the coach.
How could this be written to loop the first sub and grab the data for other coaches? Would I just change the name of the coach in the html to a string variable and then list all of the coach names? Im getting better with straight codes but looping is still a challenge given my background has nothing to do with tech.
Thanks for any and all help in advance.
Option Explicit
Public qt As QueryTable
Public ws As Worksheet
Public URL As String
Sub CoachAbileneChristian()
'School: Abilene Christian
'Coach: Joe Golding
URL = "https://www.sports-reference.com/cbb/coaches/joe-golding-1.html"
Set ws = ActiveSheet
'set up a table import (the URL; tells Excel that this query comes from a website)
Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
'tell Excel to refresh the query whenever you open the file
qt.RefreshOnFileOpen = True
'giving the query a name can help you refer to it later
qt.Name = "JoeGoldingCareer"
'you want to import column headers
qt.FieldNames = True
'need to know name or number of table to bring in
'(we'll bring in the first table)
qt.WebFormatting = xlWebFormattingNone
'qt.WebSelectionType = xlAllTables
'qt.WebTables =1
'import the data
qt.Refresh BackgroundQuery:=False
ActiveSheet.Name = "JoeGolding"
'Dim TotalGames As Range
'Set TotalGames = Range("D3:D6")
'Results = WorksheetFunction.Sum(TotalGames)
'Range("P3") = Results
Dim Results As Long
Dim TotalExperience As Range
Dim Career As Integer
Set TotalExperience = Range("B3:B6")
Results = TotalExperience.EntireRow.Count
Range("P3") = Results
Dim TotalWins As Range
Set TotalWins = Range("E3:E6")
Results = WorksheetFunction.Sum(TotalWins)
Range("Q3") = Results
Dim TotalLosses As Range
Set TotalLosses = Range("F3:F6")
Results = WorksheetFunction.Sum(TotalLosses)
Range("R3") = Results
Set ws = Sheets.Add
Sheets(1).Activate
Call CoachAirForce
End Sub
Sub CoachAirForce()
'School: Air Force
'Coach: Dave Pilipovich
URL = "https://www.sports-reference.com/cbb/coaches/dave-pilopovich-1.html"
Set ws = ActiveSheet
'set up a table import (the URL; tells Excel that this query comes from a website)
Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
'tell Excel to refresh the query whenever you open the file
qt.RefreshOnFileOpen = True
'giving the query a name can help you refer to it later
qt.Name = "DavePilipovichCareer"
'you want to import column headers
qt.FieldNames = True
'need to know name or number of table to bring in
'(we'll bring in the first table)
qt.WebFormatting = xlWebFormattingNone
'qt.WebSelectionType = xlAllTables
'qt.WebTables =1
'import the data
qt.Refresh BackgroundQuery:=False
ActiveSheet.Name = "DavePilipovich"
'Dim TotalGames As Range
'Set TotalGames = Range("D3:D6")
'Results = WorksheetFunction.Sum(TotalGames)
'Range("P3") = Results
Dim Results As Long
Dim TotalExperience As Range
Dim Career As Integer
Set TotalExperience = Range("B3:B6")
Results = TotalExperience.EntireRow.Count
Range("P3") = Results
Dim TotalWins As Range
Set TotalWins = Range("E3:E6")
Results = WorksheetFunction.Sum(TotalWins)
Range("Q3") = Results
Dim TotalLosses As Range
Set TotalLosses = Range("F3:F6")
Results = WorksheetFunction.Sum(TotalLosses)
Range("R3") = Results
Set ws = Sheets.Add
Sheets(1).Activate
End Sub
