Macro to query data from a succession of websites and display in seperate sheets

[DrSues02]

I need a macro that will go through a list of websites and query the
into a seperate list of sheets in my spreadsheet.

http://www.covers.com/data/ncf/matchups/g8_overall_1.aspx

Here is the website I need. I need this data to be pasted into Shee
1, A1.

The next website is:

http://www.covers.com/data/ncf/matchups/g8_overall_2.aspx

I need this to be pasted into Sheet 2, A1.

And so on.

However, I need it to be in Rich Text Format, and dates need to b
eliminated (these are both options that you must check before savin
the query)

I've tried manually saving each query for each webpage but this take
alot of time and I will still need a macro to run them.

I need some time of macro that will perform a query on all 55 sheets s
that I can execute the macro to update them all at once.

Is this possible? Is there an easy way to do this or am I going t
need to go through and save a query for all 55 sheets?

Any help would be appreciated.

DrSues0

 

[Dick Kusleika]

DS

When you save that first page, you get an iqy file that looks like this

WEB
1
http://www.covers.com/data/ncf/matchups/g8_overall_1.aspx

Selection=EntirePage
Formatting=RTF
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False

An iqy file is just a text file. You can copy it and change the URL in the
copy to point to the next page. I don't know if that will be much faster
than just setting up 55 queries though. If you want to automate the
creation of the queries and you have a workbook with 55 sheets in it, you
could use a macro like this

Dim i As Long

For i = 1 To 55

With Sheets(i).QueryTables.Add(Connection:= _
"URL;http://www.covers.com/data/ncf/matchups/g8_overall_" & i &
".aspx", Destination _
:=Sheets(i).Range("A1"))
.Name = "g8_overall_" & i
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebDisableDateRecognition = True
.Refresh BackgroundQuery:=False
End With
Next i

but I think you should only run that macro one time. To refresh all of
them, have a macro like this

ThisWorkbook.RefreshAll