Trying to Loop Web Query involving two changing variables - beginner question

[JTommy67]

Hi, I'm relatively new to VBA and have some questions regarding web queries in my code.

I'm pulling three different tables from the same site, and the only differences in the URL is the year for each. Each table is to go to a different sheet. As of right now, I've simply cut and pasted the same code three times, replacing the destination range and the year in the URL to get the outcome I want.

So I have the following:

Year1 = Worksheets("Records").Range("F25")
Year2 = Worksheets("Records").Range("F24")
Year3 = Worksheets("Records").Range("F23")

With Worksheets("[B]IndHitting[/B]").QueryTables.Add(Connection:= _
        "URL;http://www.baseball-reference.com/leagues/MLB/" & [B]Year1[/B] & "-standard-batting.shtml#players_standard_batting::none" _
        , Destination:=Worksheets("[B]IndHitting[/B]").Range("$A$1"))
        .Name = "none"
        .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 = """players_standard_batting""      
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False

    End With

I repeat this three times and just swap in the next worksheet and replace Year1 with Year2...and then Year3.

It works just fine but it seems less than efficient. I'd like to learn how to loop this, but I can't seem to find an answer on the web as to how to implement a loop when I have two variables changing together. I'm not sure how I would define an array or collection or if that would even be the best way to go about it.

It's just three queries, but I'll be adding some more and some of those may introduce some additional variables, so I want to make sure I'm doing this right from the get go. I'll save any additional questions for later.

Thanks

 

[Blade Hunter]

What are the three sheet names and for which year are they relative?

 

[JTommy67]

The sheets are named IndHitting (Year1), IndHitting2 (Year2), and IndHitting3(Year3)

Sorry, forgot to connect them to the years for you.

 

[Blade Hunter]

Dim X As Long
For X = 1 To 3
    With Worksheets("IndHitting" & X).QueryTables.Add(Connection:= _
        "URL;http://www.baseball-reference.com/leagues/MLB/" & Worksheets("Records").Range("F26").Offset(X * -1, 0).Text & "-standard-batting.shtml#players_standard_batting::none" _
        , Destination:=Worksheets("IndHitting").Range("$A$1"))
        .Name = "none"
        .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 = """players_standard_batting""      "
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Next

Rename IndHitting to IndHitting1 and this will work

Cheers

Dan

 

[JTommy67]

Thanks, that worked like a charm. I have a couple more questions that are related.
I need to include a web query that pulls two tables (from the same page) for three defensive positions (LF, CF, and RF) and puts them in different sheets, again, one for each year. Here's a sample of querying one of these tables, with needed variables in bold:

Year1 = Worksheets("Records").Range("F25")
Year2 = Worksheets("Records").Range("F24")
Year3 = Worksheets("Records").Range("F23")

 With Worksheets("UNNAMED<insert name="" here="" sheet="">").QueryTables.Add(Connection:= _
        "URL;http://www.baseball-reference.com/leagues/MLB/" & [B]YEAR[/B] & "-specialpos_[B]lf[/B]-fielding.shtml#players_standard_fielding_[B]lf[/B]::none" _
        , Destination:=Worksheets("UNNAMED<insert name="" here="" sheet="">").Range("$A$1"))
        .Name = "none"
        .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 = """players_advanced_fielding_[B]lf[/B]"", players_advanced_fielding_[B]lf[/B]"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Based on the recommended method, it would seem I would need to define a variable for the positions and then nest a For/Next loop inside inside of another (one for years, and one for position), but the destination worksheet needs to change (haven't named them yet) with each iteration, so my question for this part would be how to properly define the variable for positions (as an array?), and is it even possible to perform all these queries in one pass of code? My first thought is I might need to use a separate For/Next loop for each year....
Secondly, for the above query you'll notice I am pulling two tables. I'd like to get these onto the same sheet each time. This works fine when testing but I'd like the second table to be placed immediately to the right of the first instead of underneath, but I don't know how in the Web Query sub to specify separate locations for the two tables or if that's even possible.
Thanks for the reply.</insert></insert>

 

[Blade Hunter]

This "Should" work, depending on what your option base is.

Dim MySheet() As Variant, X As Long
MySheet = Array("LF", "CF", "RF")
For X = LBound(MySheet) To UBound(MySheet)
    With Worksheets("UNNAMED").QueryTables.Add(Connection:= _
        "URL;http://www.baseball-reference.com/leagues/MLB/" & Worksheets("Records").Range("F25").Offset(X * -1, 0) & "-specialpos_" & MySheet(X) & "-fielding.shtml#players_standard_fielding_" & MySheet(X) & "::none" _
        , Destination:=Worksheets("UNNAMED").Range("$A$1")) '<-- you probably want "UNNAMED" to change too, let me know your criteria
        .Name = "none"
        .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 = """players_advanced_fielding_" & MySheet(X) & """, players_advanced_fielding_" & MySheet(X) & """"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Next

This:

.Offset(X * -1, 0)

May need to change to:

.Offset((X - 1) * -1, 0)

Cheers

Dan

 

[JTommy67]

The sheets I will name Fld+Pos+Yr, so for example, FldLF1, FldLF2, ....FldRF3

I'm trying to learn as I go here. If I read that code correctly you've defined an array with the positions, and the loop iterations (specified by x = LBound to UBound) are contingent upon the number of items in the array. Is this correct? If so, what if I were to add additional positions?

Sorry I don't have the file in front of me now so I can't run this through. Will get to it in the morning.

Thanks for your help.

 

[Blade Hunter]

That's correct, if you add positions it will expect corresponding years.

 

[JTommy67]

So If I were to want to add positions and still return three years for each, the array approach here would not work. The reason I'm bringing this up is because it now looks like I'll need 4 or 5 positions, 3 years each. Instead, could I do a nest a For/Next inside another:

For X = 1 to 3 (iteration for each year)

 

[JTommy67]

sorry double post