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

 

[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)

                dim Pos as String

                For P = 1 to 5 (for position)

                Series of IF/ElseIF which define Pos (If P=1 Then Pos='RF" for example)

And then run the query

 

[JTommy67]

Sorry for the poor formatting and double posting. I can't figure out what gives on these boards. It's not letting me edit properly.

 

[Blade Hunter]

That sounds like it will work, I would probably use a case statement instead of if / elseif but it really doesn't matter too much.

Alternatively you could have two arrays and loop one inside the other.

Give it a bash and if you get stuck yell out and I will help you .

Cheers

Dan

 

[JTommy67]

Definitely making progress. I'm gonna stick with If/Elseif since I'm unfamiliar with Case statements. Everything seems to be working except for one more thing, and I don't even know if this is possible, but I would like both tables in the query to be side by side, but I can't see how to designate a destination for the second table. I suppose I could add some code to move it manually, but I'd rather not mess with that if there's a way to specify a location within the query itself. Any thoughts, here?

 

[JTommy67]

The above request is not crucial. I'm leaning towards putting the second table on a different sheet, anyway.

A few more questions:

I've been running some code that cleans up these sheets and formats them according to my needs.

To delete category lines:

For X = 1 To 3
    
        Worksheets("Batt" & X).Select
            row_number = 1
            search_string = "Rk" 'Look for cells with Rk
        Do
        DoEvents
            row_number = row_number + 1
            item_in_review = Worksheets("Batt" & X).Range("A" & row_number)
            If InStr(item_in_review, search_string) Then
                Worksheets("Batt" & X).Range("A" & row_number).Select
                ActiveCell.EntireRow.Delete
            End If
        Loop Until Worksheets("Batt" & X).Range("A" & row_number).Value = ""
    
    Next

And to remove special characters from name cells (asterisks and pound signs):

For X = 1 To 3
    
        Worksheets("Batt" & X).Select
            row_number = 1
            row_end = row_number
    
        Do
            Worksheets("Batt" & X).Range("A" & row_end).Select 
            row_end = row_end + 1
        Loop Until Worksheets("Batt" & X).Range("A" & row_end).Value = ""
 
     
        Worksheets("Batt" & X).Range("B1:B" & row_end).Select
            Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
    
            Selection.Replace What:="#", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
    Next

However, I realize now that using the .Select method is inefficient and slow, so I'd like to insert some code into the web query For/Next loop to execute these commands there. I've seen a couple of snippets of code that people have used and inserted them into the Web Query loop: (highlighted in bold)

Dim X As Long
[B]Dim LastRow As Long
Dim r As Excel.Range[/B]   
 
    For X = 1 To 3
       
    Worksheets("Batt" & X).Cells.Clear

        With Worksheets("Batt" & X).QueryTables.Add(Connection:= _
            "URL;http://www.baseball-reference.com/leagues/MLB/" & Worksheets("Position Players").Range("F26").Offset(X * -1, 0).Text & "-standard-
            batting.shtml#players_standard_batting::none" _
            , Destination:=Worksheets("Batt" & X).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 = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
                
           [B]LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            Set r = .Range(Cells(2, "A"), Cells(LastRow, "A"))
            With r
                .AutoFilter Field:=1, Criteria1:="*Total*", Operator:=xlFilterValues
                .SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End With[/B]   
         
        End With
    
        Dim i As Integer
            For i = QueryTables.Count To 1 Step -1
                If InStr(QueryTables(i).Connection, "URL;") = 1 Then QueryTables(i).Delete
            Next
        
    Next

This is returning an error (Object doesn't support this property or method) on the line "LastRow = .Range("A" & .Rows.Count).End(xlUp).Row".
So, I've gone wrong somewhere.

Also, I added a snippet at the end which presumably deletes the web queries. Is this in the right place?

As for getting rid of # and * signs, I haven't tried anything yet.

Thanks for your patience. I had everything I wanted to code working perfectly, but I decided to go back through and tighten everything up and make sure things were being executed more efficiently, so I keep running across places that need changed.

 

[JTommy67]

The above issues with respect to removing category lines as well as # and * signs has been solved. Still remaining is the question as to whether or not a destination can be specified for the second table of a web query.