Copying and pasting a web query

[Jeffrey Green]

I have a good web query set up, but now I need to replicate it 600 times . . .each baseball team, for the last 20 years.

The only difference between URLs is the team abbreviation, and the year. So instead of ARI/1997, I need ARI/1996, etc. then DET/1997 then DET/1996.

Is there an "easy" way of just copying what I have and changing the URL?

Thanks

 

[John_w]

Create the web query manually with the macro recorder running. Then edit the code to modify the web query's Connection parameter for each URL.

 

[Jeffrey Green]

Thanks, but I don't understand what part of the process I would record in a macro . . .

thanks

 

[Jeffrey Green]

Ok, I got it, but is there any way to modify the macro so that it puts the new data in the selected cell? Do I have to change the Range and Destination??

All I would want to do is change "1995" to "1996" and just keep appending my rows as I add years . . .

this is my macro . .

Range("Q2259").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.baseball-reference.com/teams/DET/1995-schedule-scores.shtml", _
Destination:=Range("$Q$2259"))
.Name = "1995-schedule-scores.shtml"

 

[Jeffrey Green]

Re: Copying and pasting a web query, part 2

After I ran created my macro, here is what it looks like...

Range("Q2259").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.baseball-reference.com/teams/DET/1995-schedule-scores.shtml", _
Destination:=Range("$Q$2259"))
.Name = "1995-schedule-scores.shtml"

Now I want the next web query to start in cell Q2411.

I tried changing the macro to to DESTINATION = $Q$2411 . . . but it doesnt work. It is creating an entire new columns and pushing everything over.

Help,

 

[Jeffrey Green]

What would be ideal would be a macro that prompts me for the Year and the 3 character team code . . DET, LAA, CHI, NYY, etc.

Then insert the data 163 rows down with each each year . . .

Clueless on how to do that . . .

 

[John_w]

Which data are you importing? You haven't said, nor have you posted enough of the web query code for me to see which table is being retrieved.

Try something based on the code below, which retrieves the "Team Game-by-Game Schedule and Results" table for the entered team code and year.

Sub Get_Data()
   
    Dim teamYear As String, team As String, year As String
    Dim destinationRow As Long
    
    teamYear = ""
    destinationRow = 1
    
    Do
    
        teamYear = InputBox("Enter team code and year separated by a space", Default:=teamYear)
        
        If teamYear <> "" Then
        
            team = Split(teamYear, " ")(0)
            year = Split(teamYear, " ")(1)
            
            With ActiveSheet.QueryTables.Add(Connection:= _
                "URL;http://www.baseball-reference.com/teams/" & team & "/" & year & "-schedule-scores.shtml", _
                Destination:=Range("A" & destinationRow))
                .Name = "schedule-scores.shtml"
                .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 = """team_schedule"""
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
                .Refresh BackgroundQuery:=False
            End With
        
            ActiveSheet.QueryTables(1).Delete
            
            destinationRow = destinationRow + 168
            Range("A" & destinationRow).Select

        End If
    
    Loop Until teamYear = ""
    
End Sub

 

[Jeffrey Green]

This worked (I think) the first time I tried it.

After that, it acted like it working, moving to the next "destination cell", then it quit working . . . . when I go back to the empty rows, where it should have pasted data, the cells are all blank . .

Was there anything I needed to "tweek" on your query before trying it?

I just copied and pasted it, as is, into a module.

Thanks!!

 

[Jeffrey Green]

Yea, now when I run it, it doesn't do the web query . . When I click on a cell that should be populated, I don't get the EDIT QUERY, REFRESH, etc. option on a right click . . .

Thanks Again!

 

[Jeffrey Green]

I am on a Macintosh, and have to install a bunch of updates on my Windows 7 partition of my parallels . . I can post the rest of the code if you need it, but I think I got yours work again.

Next favor, if possible, If I created a named range (call it "Teams" containing a list of all the teams and all of the years, in your format, would it be possible to have the macro loop through the list? I am running Excel 2007, so I have plenty of rows.

For example

Det 1990
Det 1991
Det 1992
skip a few
Bal 1990
Bal 1991
Bal 1992
etc.

There would be 30 (teams) times 20 (years) time 162 (games per year) rows. (total 97,300 rows approximately)

???