Duplicate web query's ( Different Years?)


Well-known Member
Nov 18, 2003
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


Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
        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>
Upvote 0
Thanks for the reply...

When I run it I get an error on

.WebDisableRedirections = False

THis commad? any ideas?
Upvote 0
Try removing it from the code. This is probably a version issue. Should work without it though.
Upvote 0
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?
Upvote 0
Yeah...it only pulled 98-02 for me. Have you tried getting this information manually from the website?
Upvote 0
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..
Upvote 0
For Example:
Below is 4 of the 30 Query addresses's



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
Upvote 0
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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back