Downloading a lot of data from web

[small caps]

Hi, I have to download a lot of data from the web. Data is in a timespan of
10 years (1.1.1997. until today which is about 365x11 sheets in excel), URLs
are distinguished by date http://something/DATE/somethingelse/ I have it in
two places. In one format of the date in URL is yyyy/mm/dd in another it is
mm.dd.yyyy. Can someone tell me the code that:
-cycles through the DATEs (I know how to enter date by separating it in
three variables Year, Month and Day but making few for...next loops with a
lot of exceptions doesn't seem to be the best way when dates are in 39xyz
format) and transforms them in another format so they can be joined with
http://something
-code that in Workbook named Table1997.xls opens a new worksheet in which it
pastes data taken from the URL (I intend to loop it through all the dates)
Any help is appreciated

 

[Don Guillett]

Perhaps you could let us see some actual urls and data layout desired. I do
this sort of thing often.

 

[small caps]

Data is under the link:
http://www.wunderground.com/history..._city=Kranj&req_state=&req_statename=Slovenia
(this is example for the 3rd dec 2008). Data desired is table under "hourly
observations" (when I insert query it is marked like "21"). Afterwards I
clean the data and arrange it. I'd like to write a VBA that upon entering
two dates e.g. entering 01.01.2002. in fields A2,B2,C2 and date 03.12.2008.
in fields A4,B4,C4 and starting a macro loops through all this URL's and
puts each hourly observations table in:
a) different sheet (but all in one Workbook), sheet is named by a date, e.g.
01.01.2002. and
b) different workbook named by a date like 01-01-2002 and a sheet by the
same name.
Thank you for your time,

small caps

 

[small caps]

Data is under the link:
http://www.wunderground.com/history..._city=Kranj&req_state=&req_statename=Slovenia
(this is example for the 3rd dec 2008). Data desired is table under "hourly
observations" (when I insert query it is marked like "21"). Afterwards I
clean the data and arrange it. I'd like to write a VBA that upon entering
two dates e.g. entering 01.01.2002. in fields A2,B2,C2 and date 03.12.2008.
in fields A4,B4,C4 and starting a macro loops through all this URL's and
puts each hourly observations table in:
a) different sheet (but all in one Workbook), sheet is named by a date, e.g.
01.01.2002. and
b) different workbook named by a date like 01-01-2002 and a sheet by the
same name.
Thank you for your time,

small caps

 

[Don Guillett]

The trick is to establish the query with the base url. Do NOT add another
query each time. Refresh the existing within a looping macro that uses the
variable as part of the url string. Then the macro would copy wherever
desired. More detail needed as to what part of each table and if you want
each day on a separate sheet named for the day. You may contact me privately
at my address below. This is the sort of thing I do for clients daily.