Hello,
I am sure this is a simple task but i am quite new to macros and VBs.
I have a list of players from my cricket team and the link to their play cricket web page and their unique player ID.
I know how to set up a web query, however i was hoping there was a quick way to automate the setup. I have around 100 players and i do not wish to spend 2 days setting up web queries for them all. I have recorded the following macro:
Sub PlayCricket()
'
' PlayCricket Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://hitchin.play-cricket.com/scoreboard/individualPerformanceResults.asp?seasonID=26&userID=10532067&ruleTypeID=6101" _
, Destination:=Range("$A$1"))
.Name = _
"individualPerformanceResults.asp?seasonID=26&userID=10532067&ruleTypeID=6101"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("O1").Select
Selection.AutoFill Destination:=Range("O1:O5"), Type:=xlFillDefault
Range("O1:O5").Select
End Sub
The URL - (URL;Hitchin CC - Play-Cricket - Individual Performances for SeasonuserID=10532067&ruleTypeID=6101) will be the same for each player apart from the 'userID'. Each player will have there own ID.
I have a list of all the full URLs as above (with the unique IDs) and i also have extracted all the user IDs.
<colgroup><col><col><col></colgroup><tbody>
</tbody>Table 1
What I would like is to be able to run a macro to set up all players in one worksheet (one after the other) then from there i would like to just open the workbook and press update for the new information. Below is the result of the above macro:
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>Table 2
What i would also like is the macro to fill the players name from Table 1 column A to auto fill in columns o in Table 2. Which i am guessing is a bit more work. A1-N2 are headers from the web site, so each player will have these.
I would really preffer this all to be contanied in one sheet and when it is updated the rows will all be shifted down. So next week when another game is played Paul Smiths next week will go in row 6 and the following players information will be shifted down, and so on.
I hope this is enough information and you help will be very welcome.
Regards
I am sure this is a simple task but i am quite new to macros and VBs.
I have a list of players from my cricket team and the link to their play cricket web page and their unique player ID.
I know how to set up a web query, however i was hoping there was a quick way to automate the setup. I have around 100 players and i do not wish to spend 2 days setting up web queries for them all. I have recorded the following macro:
Sub PlayCricket()
'
' PlayCricket Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://hitchin.play-cricket.com/scoreboard/individualPerformanceResults.asp?seasonID=26&userID=10532067&ruleTypeID=6101" _
, Destination:=Range("$A$1"))
.Name = _
"individualPerformanceResults.asp?seasonID=26&userID=10532067&ruleTypeID=6101"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("O1").Select
Selection.AutoFill Destination:=Range("O1:O5"), Type:=xlFillDefault
Range("O1:O5").Select
End Sub
The URL - (URL;Hitchin CC - Play-Cricket - Individual Performances for SeasonuserID=10532067&ruleTypeID=6101) will be the same for each player apart from the 'userID'. Each player will have there own ID.
I have a list of all the full URLs as above (with the unique IDs) and i also have extracted all the user IDs.
| A | B | C |
| Name | ID | URL |
| Paul Smith | 10532067 | Hitchin CC - Play-Cricket - Individual Performances for Season |
| Ben Williams | 12316605 | Hitchin CC - Play-Cricket - Individual Performances for Season |
| Richard Middleditch | 10532067 | http://hitchin.play-cricket.com/scoreboard/individualPerformanceResults.asp?seasonID=26&userID=10532067&ruleTypeID=6101 |
| Joe Elliot | 11552467 | Hitchin CC - Play-Cricket - Individual Performances for Season |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
What I would like is to be able to run a macro to set up all players in one worksheet (one after the other) then from there i would like to just open the workbook and press update for the new information. Below is the result of the above macro:
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O |
| Fixture | Batting | Bowling | Fielding | Wicket-keeping | Paul Smith | |||||||||
| Date | Home -vs- Away | Type | Inn | Runs | Overs | Mdns | Runs | Wkts | Ct | ROs | Ct | St | Paul Smith | |
| Click to view scorecard | 20/04/2013 | Letchworth Garden City CC - 1st XI -vs- 1st XI | Friendly | 1 | 7 | - | - | - | - | 0 | 0 | 0 | 0 | Paul Smith |
| Click to view scorecard | 27/04/2013 | Preston CC, Herts - 1st XI -vs- 1st XI | Friendly | 1 | 18 | 3 | 0 | 15 | 1 | 0 | 0 | 0 | 0 | Paul Smith |
| Click to view scorecard | 11/05/2013 | Flitwick CC - 1st XI -vs- 1st XI | League | 1 | 3 | - | - | - | - | 0 | 0 | 0 | 0 | Paul Smith |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
What i would also like is the macro to fill the players name from Table 1 column A to auto fill in columns o in Table 2. Which i am guessing is a bit more work. A1-N2 are headers from the web site, so each player will have these.
I would really preffer this all to be contanied in one sheet and when it is updated the rows will all be shifted down. So next week when another game is played Paul Smiths next week will go in row 6 and the following players information will be shifted down, and so on.
I hope this is enough information and you help will be very welcome.
Regards
