Hello all,
I have the following code which works great. I have 3 issues however. First here is the code and then the questions will follow
Option Explicit
Sub CoachAbileneChristian()
Dim qt As QueryTable
Dim ws As Worksheet
Dim URL As String
URL = "https://www.sports-reference.com/cbb/coaches/joe-golding-1.html"
Set ws = ActiveSheet
Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
qt.RefreshOnFileOpen = True
qt.Name = "JoeGoldingCareer"
qt.FieldNames = True
qt.WebFormatting = xlWebFormattingNone
qt.Refresh BackgroundQuery:=False
ActiveSheet.Name = "JoeGolding"
Dim TotalGames As Range
Dim Results As Long
Set TotalGames = Range("D3:D6")
Results = WorksheetFunction.Sum(TotalGames)
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
Dim TotalExperience As Range
Dim Career As Integer
Set TotalExperience = Range("B3:B6")
Results = TotalExperience.EntireRow.Count
Range("O3") = Results
End Sub
I know this is not the most efficient way however I must crawl before I walk. My questions are as follows... when the code runs it imports tables from the site noted, and then sums the total coach experience as well as total games and wins and losses. What I need to do is then be able to export those 4 results to a specific Excel file outside of this workbook, to a specific page in that workbook and of course to the specific cell within that sheet to update these values.
1) Could someone kindly demonstrate how this would be done to the end of my code? (Sending those totals to specific saved filed elsewhere on my PC)
2) I have done one record for one coach. I now want to repeat this process for dozens of coaches. Each would obviously have their own webpage, more and less experience etc. How would I best be advised to code that? I do NOT want to include data for the 2017-18 season, just everything prior to this year. Do I use a different sub for each coach? and how would i link those subs? or do i keep basically copying and pasting this code below itself changing the applicable items ie webpage etc for each coach?
3)This code takes place on Sheet1 (which I renamed after the coach name) what adjustment could I make so that I could run a macro and have it do JoeGolding on sheet1, then open a new worksheet and perform the same code on Sheet2 for that coach, and then open Sheet3 and perform the code for that specific coach and so on, finally saving the results and closing down
I thank you all for your time in advance, its very much appreciated.
I have the following code which works great. I have 3 issues however. First here is the code and then the questions will follow
Option Explicit
Sub CoachAbileneChristian()
Dim qt As QueryTable
Dim ws As Worksheet
Dim URL As String
URL = "https://www.sports-reference.com/cbb/coaches/joe-golding-1.html"
Set ws = ActiveSheet
Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
qt.RefreshOnFileOpen = True
qt.Name = "JoeGoldingCareer"
qt.FieldNames = True
qt.WebFormatting = xlWebFormattingNone
qt.Refresh BackgroundQuery:=False
ActiveSheet.Name = "JoeGolding"
Dim TotalGames As Range
Dim Results As Long
Set TotalGames = Range("D3:D6")
Results = WorksheetFunction.Sum(TotalGames)
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
Dim TotalExperience As Range
Dim Career As Integer
Set TotalExperience = Range("B3:B6")
Results = TotalExperience.EntireRow.Count
Range("O3") = Results
End Sub
I know this is not the most efficient way however I must crawl before I walk. My questions are as follows... when the code runs it imports tables from the site noted, and then sums the total coach experience as well as total games and wins and losses. What I need to do is then be able to export those 4 results to a specific Excel file outside of this workbook, to a specific page in that workbook and of course to the specific cell within that sheet to update these values.
1) Could someone kindly demonstrate how this would be done to the end of my code? (Sending those totals to specific saved filed elsewhere on my PC)
2) I have done one record for one coach. I now want to repeat this process for dozens of coaches. Each would obviously have their own webpage, more and less experience etc. How would I best be advised to code that? I do NOT want to include data for the 2017-18 season, just everything prior to this year. Do I use a different sub for each coach? and how would i link those subs? or do i keep basically copying and pasting this code below itself changing the applicable items ie webpage etc for each coach?
3)This code takes place on Sheet1 (which I renamed after the coach name) what adjustment could I make so that I could run a macro and have it do JoeGolding on sheet1, then open a new worksheet and perform the same code on Sheet2 for that coach, and then open Sheet3 and perform the code for that specific coach and so on, finally saving the results and closing down
I thank you all for your time in advance, its very much appreciated.
