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

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/match...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/match...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

--
DrSues0
-----------------------------------------------------------------------
DrSues02's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread.php?threadid=27065

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

DS

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

WEB
1
http://www.covers.com/data/ncf/match...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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


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

I've tried manually saving each query for each webpage but this takes
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 so
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 to
need to go through and save a query for all 55 sheets?

Any help would be appreciated.

DrSues02


--
DrSues02
------------------------------------------------------------------------
DrSues02's Profile:
http://www.excelforum.com/member.php...o&userid=12569
View this thread: http://www.excelforum.com/showthread...hreadid=270651