Retrieving Web Data
 

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

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

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


Thanks

--
leafsfan196
-----------------------------------------------------------------------
leafsfan1967's Profile: http://www.excelforum.com/member.php...fo&userid=2435
View this thread: http://www.excelforum.com/showthread.php?threadid=38170

Retrieving Web Data
 

Anybody out there

--
leafsfan196
-----------------------------------------------------------------------
leafsfan1967's Profile: http://www.excelforum.com/member.php...fo&userid=2435
View this thread: http://www.excelforum.com/showthread.php?threadid=38170

Retrieving Web Data
 

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381707

Retrieving Web Data
 

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

--
leafsfan196
-----------------------------------------------------------------------
leafsfan1967's Profile: http://www.excelforum.com/member.php...fo&userid=2435
View this thread: http://www.excelforum.com/showthread.php?threadid=38170

Retrieving Web Data
 

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

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=38170

Retrieving Web Data
 

Yes, I can copy/paste the links into Excel

--
leafsfan196
-----------------------------------------------------------------------
leafsfan1967's Profile: http://www.excelforum.com/member.php...fo&userid=2435
View this thread: http://www.excelforum.com/showthread.php?threadid=38170

Retrieving Web Data
 

This will take link values from cell E1,E2,E3 , but it can programmed t
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 Su

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=38170