Web Query Macro-Difficult

[Joshua0317]

Hello,

I found a nice Statistics site I would like to use for my Fantasy Football league. It has all the stats for each week and position selectable from a drop down menu on the webpage. I recorded and altered a macro that will run through the numbers 1-16 for the weeks of play. I then changed it for each positions (QB, RB, WR, TE). The macro pulls each week without an issue. That gave me some hope. But when I switched the position for the next worksheet, it gave me a repeat of the QB data. Below is my code with the the portions I change highlighted.

Sub QBDATA()
'
' Macro2 Macro
'


Dim Part1, Part2, Part3, URL, TEMPNAME As String
Part1 = "URL;http://wp.advancednflstats.com/playerstatsWeek.php?week="
Part2 = "&posQB"
Part3 = "playerstatsWeek.php?week="
Dim i As Integer
i = 1
Do Until i = 17
URL = Part1 & i & Part2
TEMPNAME = Part3 & i & Part2


With ActiveSheet.QueryTables.Add(Connection:= _
URL, _
Destination:=Cells(i * 40 - 39, 2))
.Name = TEMPNAME
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

End With

i = i + 1
Loop
End Sub


The original site I am getting the information from is:

http://wp.advancednflstats.com/playerstatsWeek.php?week=1&pos=RB

(Running Back week 1)

My Draft is on Thursday and I have been working on this for a week now. Any help would be a miracle.

Thanks all.

 

[drsarao]

Joshua,
You forgot the equal sign.
Part2 = "&pos=QB"
The default must be QB hence you get the same data.
Hopefully this will solve your problem.
Why don't you make another loop for QB, RB etc and bump the column count in Destination:=Cells(i * 40 - 39, 2)), by 10?

 

[Joshua0317]

Thanks so much. No wonder all the sytax of VBAlooked correct, because it was. The URL was wrong. Duh. I like the suggestion, but I am making them separate so that I don't have to renew them every position every time. I change it to its final version now that always places the new info at the bottom of the data. And the made a sub that calls all the positions at once if I want. If you would like the code let me know. Thanks for the help.

 

[drsarao]

You are welcome.
An enhanced version:

Option Base 1 'needed to start array numbering from 1 in plr
Sub PLAYERDATA()
Dim Part1, Part2, Part3, URL, TEMPNAME As String
Dim plr
plr = Array("QB", "RB", "WR", "TE")
Part1 = "URL;http://wp.advancednflstats.com/playerstatsWeek.php?week="
Part3 = "playerstatsWeek.php?week="
Dim i As Integer
For j = 1 To 4
    Part2 = "&pos" & plr(j)
    Cells(1, j * 18 - 16) = plr(j) & " PLAYERS"
    For i = 1 To 2 ' 16
        URL = Part1 & i & Part2
        TEMPNAME = Part3 & i & Part2
        With ActiveSheet.QueryTables.Add(Connection:= _
           URL, Destination:=Cells(i * 40 - 38, j * 18 - 16))
            .Name = TEMPNAME
            .PreserveFormatting = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .WebSelectionType = xlAllTables
            .BackgroundQuery = False
            .Refresh
            .Delete 'query is not going to be refreshed again
                    'so as well delete it to reduce file size
        End With
    Next i
Next j
End Sub