Dynamic Web Query

[dragonfyre77]

Hello,

I am trying to build a database of basketball player stats by using boxscores. I have built a page the list all the games on a particular day and I'm looking to build a macro that will allow me to pull the boxscore table of all the games played on a date specified. I was thinking I would be able to use an input box to enter the date and then excel would search my list and pull the boxscores into a separate sheet for each game played that day. The boxscores are loaded on to an html page and are static.

Currently the Schedule sheet I have created has the following Header Columns:

DATE, TEXT DATE, VISITOR TEAM, HOME TEAM, URL (PATH)

I have tried to build a macro for this but I can't paste the contents of the URL column into the address box. I'm not sure how to get the path into the address box and then call the next page.

Am I going about this the right way? Do I need more information on my schedule page? Is what I'm trying to possible with only Excel (which is my preference) or do I need a Windows script writer? Any help on this problem would be appreciated.

Thanks,
J

 

[dragonfyre77]

I have tried to start building some of the code for the query I'm trying to create and I'm trying to piece it together in parts. I've started with the web query because I think this may be my biggest sticking point. Thus far I have come up with the following:

Sub BoxscoreVariables()
vDate = Range("A1").Select
vAwayCode = Range("A2").Select
vHomeCode = Range("A3").Select
vGameCode = Range("A4").Select
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.basketball-reference.com/boxscores/" & vGameCode, Destination:=Range("$A$1"))
.Name = vGameCode
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = vAwayCode & "_basic" & vHomeCode & "_basic"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With
End Sub

The values in cells A2, A3 & A4 are SAC, SAS & 201104060SAS respectively.

However, when I run the macro all that seems to happen is that the new sheet is created but no data is pulled from my source.

Essentially, I am trying to ask the macro to use the values in each of these cells to pull the appropriate information. I have created a table that lists that whole schedule so I plan on creating several queries and need to figure out how to get the variables to work for this test boxscore before I move on to the next phase.

Any suggestions would be greatly appreciated.

J

 

[dragonfyre77]

So I've gone back to the drawing board and made some changes after reading some of the posts in the forum. Here is what I have come up with:

Sub boxscore_retrieval()
Dim a As Integer, x As Integer
Dim b As String, c As String, d As String
x = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
b = Worksheets("sheet1").Cells(a, 4)
c = Worksheets("sheet1").Cells(a, 2)
d = Worksheets("sheet1").Cells(a, 3)
Sheets.Add.Name = b
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;www.basketball-reference.com/boxscores/" & b, Destination _
:=Worksheets(b).Range("A1"))
.Name = b
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebTables = c,d
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = True
.WebDisableRedirections = False
End With
Next a
End Sub

The values in cells A2, A3 & A4 are SAC_basic, SAS_basic & 201104060SAS respectively.

The code executes fine until ".WebTables = c,d" line. With this line of code I get a compile error; expected:end of statement and it highlights the comma. Then when I remove the second table and I get debug error that states "application-defined or object-defined error".

I should be able to pull both tables as the initial macro I recorded did just that and works fine. How do I get my macro to pull the two tables that I want? Have I set something up incorrectly?

Any suggestions would be greatly appreciated.

Thanks.