Duplicate web query's ( Different Years?)

[gg]

Guy's I am trying to run a web query that has several years associated.
The data is in the same format. However, the website is Year specific.

Is there a way that I can change the web address with in the query's
so that I don't have to re-setup each of the 30 query's for each year?

On one work sheet I have all 2002 query's
I want to be able to find and replce the "2002" with "2001" etc...
Below is the address for the 2002 data

http://pytho.nss.net/nfl/(nt50p1452tre4j45xqc031jn)/pylog.aspx?team=29&season=2002

 

[TommyGun]

Using the macro recorder I got the necessary info, and then made slight edits to expand it to 30 years.

See if this works for you...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> BASECONNECTION <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "URL;http://pytho.nss.net/nfl/(nt50p1452tre4j45xqc031jn)/pylog.aspx?team=29&season="

<SPAN style="color:#00007F">Sub</SPAN> GetData()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> i = (2002 - 30) <SPAN style="color:#00007F">To</SPAN> 2002
        ActiveWorkbook.Sheets.Add
        
        ActiveSheet.Name = i
        
        <SPAN style="color:#00007F">With</SPAN> ActiveSheet.QueryTables.Add(Connection:= _
            BASECONNECTION & <SPAN style="color:#00007F">CStr</SPAN>(i), Destination:=Range("A1"))
            .Name = "pylog.aspx?team=29&season=2002"
            .FieldNames = <SPAN style="color:#00007F">True</SPAN>
            .RowNumbers = <SPAN style="color:#00007F">False</SPAN>
            .FillAdjacentFormulas = <SPAN style="color:#00007F">False</SPAN>
            .PreserveFormatting = <SPAN style="color:#00007F">True</SPAN>
            .RefreshOnFileOpen = <SPAN style="color:#00007F">False</SPAN>
            .BackgroundQuery = <SPAN style="color:#00007F">True</SPAN>
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = <SPAN style="color:#00007F">False</SPAN>
            .SaveData = <SPAN style="color:#00007F">True</SPAN>
            .AdjustColumnWidth = <SPAN style="color:#00007F">True</SPAN>
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = """logTable"",""summaryTable"""
            .WebPreFormattedTextToColumns = <SPAN style="color:#00007F">True</SPAN>
            .WebConsecutiveDelimitersAsOne = <SPAN style="color:#00007F">True</SPAN>
            .WebSingleBlockTextImport = <SPAN style="color:#00007F">False</SPAN>
            .WebDisableDateRecognition = <SPAN style="color:#00007F">False</SPAN>
            .WebDisableRedirections = <SPAN style="color:#00007F">False</SPAN>
            .Refresh BackgroundQuery:=<SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[gg]

Tommy,
Thanks for the reply...

When I run it I get an error on

.WebDisableRedirections = False

THis commad? any ideas?

 

[TommyGun]

Try removing it from the code. This is probably a version issue. Should work without it though.

 

[gg]

Tommy,
That worked beautiful...

However, I do have what I think is a small glitch...

This only pulled the last of the 30 query's?
Is there a way it can pull all of them?

 

[TommyGun]

Yeah...it only pulled 98-02 for me. Have you tried getting this information manually from the website?

 

[gg]

I can pull it all by hand...
But I thought there may be some special macro that I could do it quickly?

Becasue I have several others like it to do..

 

[TommyGun]

Is the URL for every year exactly the same except for the final parameter?

 

[gg]

For Example:
Below is 4 of the 30 Query addresses's

2002
http://pytho.nss.net/nfl/(n0hjynnzriga1dzzeqht2y3r)/pylog.aspx?team=4&season=2002
http://pytho.nss.net/nfl/(n0hjynnzriga1dzzeqht2y3r)/pylog.aspx?team=17&season=2002
http://pytho.nss.net/nfl/(n0hjynnzriga1dzzeqht2y3r)/pylog.aspx?team=19&season=2002

2001
http://pytho.nss.net/nfl/(n0hjynnzriga1dzzeqht2y3r)/pylog.aspx?team=4&season=2001
http://pytho.nss.net/nfl/(n0hjynnzriga1dzzeqht2y3r)/pylog.aspx?team=17&season=2001
http://pytho.nss.net/nfl/(n0hjynnzriga1dzzeqht2y3r)/pylog.aspx?team=19&season=2001
http://pytho.nss.net/nfl/(n0hjynnzriga1dzzeqht2y3r)/pylog.aspx?team=22&season=2001

The numbers high lighted are one parameter and the year is the other.
I need the years to be on the same worksheet.

Does this help

 

[gg]

I just wanted to bump this subject back to the top.
I think this can be done but I am not sure?

If anybody has any idea please let me know.
Thanks