Retrieving Web Data

Using web queries is new to me but was able to download a table into Excel. However, what I want to do, is download several pieces of data from one website but each different table is on a different link on the site. And, each day, the links change because the links are related directly to the dates.

Is there a way for Excel to retrieve the same data but from different links? So if there are 5 different pages each with the same structure of data (same layout and everything) but different numbers.

This is new to me so maybe if someone could please direct me to a link that explains this or explain it to me directly.


Thanks.

Anybody out there?

This will download data from three links ,the data will be displayed in excel one after the other.






Sub Macro7()


Dim t, t1 As Variant
Range("D1").Select
With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.yahoo.com", _
Destination:=Range("A1"))
.Name = "www.yahoo"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
t = ActiveSheet.UsedRange.Address
MsgBox t
t1 = Split(t, ":")
If UBound(t1) > 0 Then
Range("a" & Range(t1(1)).Row).Offset(1, 0).Select
Else
Range("a1").Select
End If


With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.nba.com", _
Destination:=Range("A26"))
.Name = "www.hotmail"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
t = ActiveSheet.UsedRange.Address
t1 = Split(t, ":")
If UBound(t1) > 0 Then
Range("a" & Range(t1(1)).Row).Offset(1, 0).Select
Else
Range("a1").Select
End If
With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.cnn.com", _
Destination:=Range("A28"))
.Name = "www.cnn"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Range("A22").Select
End Sub

I know how to do that. My question was if I can download from 3 different links that CHANGE everyday. For example, if each business day's report has its own seperate link corresponding to each day, is there a way (I can't imagine) of locating each day's link and downloading that way? So you don't necessarily know the direct link but have the link where you can choose the present day.

If you know where the links are ,may be in excel cells , you can dynamically collect the links and download data. Where exactly is the links available

Yes, I can copy/paste the links into Excel.

This will take link values from cell E1,E2,E3 , but it can programmed to take from any excelworkbook and any worksheet.

try this and let me know

Sub Macro7()
dim first_link,second_link,third_link,w_row as variant
first_link=range("e1").value
second_link=range("e2").value
third_link=range("e3").value

Dim t, t1 As Variant
Range("D1").Select
With ActiveSheet.QueryTables.Add(Connection:="URL;" & first_link , _
Destination:=Range("A1"))
.Name = "www.yahoo"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
t = ActiveSheet.UsedRange.Address
MsgBox t
t1 = Split(t, ":")
If UBound(t1) > 0 Then
Range("a" & Range(t1(1)).Row).Offset(1, 0).Select
w_row=selection.row
Else
Range("a1").Select
w_row=2
End If


With ActiveSheet.QueryTables.Add(Connection:="URL;" & second_link, _
Destination:=Range("A" & w_row))
.Name = "www.hotmail"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
t = ActiveSheet.UsedRange.Address
t1 = Split(t, ":")
If UBound(t1) > 0 Then
Range("a" & Range(t1(1)).Row).Offset(1, 0).Select
w_row=selection.row
Else
Range("a1").Select
w_row=2
End If
With ActiveSheet.QueryTables.Add(Connection:="URL;" & third_link, _
Destination:=Range("A" & w_row ))
.Name = "www.cnn"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub