Hello! Please help me, this is the code I have so far which I have found online.
Private Const URL_TEMPLATE As String = "URL;http://www.nhl.com/ice/playerstats.htm?fetchKey=20142ALLSASALL&viewName=assists&sort=player.bioFirstNameLastName&pg={0}"
Private Const NUMBER_OF_PAGES As Byte = 15
Sub test()
Dim page As Byte
Dim queryTableObject As QueryTable
Dim url As String
For page = 1 To NUMBER_OF_PAGES
url = VBA.Strings.Replace(URL_TEMPLATE, "{0}", page)
Set queryTableObject = ActiveSheet.QueryTables.Add(Connection:=url, Destination:=ThisWorkbook.Worksheets.Add.[a1])
queryTableObject.WebSelectionType = xlSpecifiedTables
queryTableObject.WebTables = "6"
queryTableObject.Refresh
Next page
End Sub
The only modification I have made is change the WebTables and the target URL. How can I edit this so that each new page query will add the returned data on the same sheet, underneath the previous page?
As it is now it creates a new worksheet for every page query. Thanks!
Bookmarks