G
Guest
I use the following macro to extract data and it works fine....
----------------
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hrimes.com/137.dat", Destination:=Range("A1"))
.Name = "137"
.PreserveFormatting = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.Refresh BackgroundQuery:=False
End With
-----------------
However I now need to consolidate data from a range of sources so need to
amend these lines.
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hrimes.com/137.dat", Destination:=Range("A1"))
.Name = "137"
The data sources will be in a list in Sheet2 in col C. At present there are
6 of these but they could increase dramatically.
So what I'm trying to do is to get the macro to use the list in Col C of
Sheet2 to determine the specific filename at www.hrimes.com
and change the Destination from Range("A1") to the next available cell in
Col B of Sheet1 so each range follows on directly from the other.
So, I've changed the macro (please see below) and appear to have made a hash
of it. The first stage of retrieving the data isn't working and therefore I
haven't been able to test the destination code either. I've snipped the
lines that are the same for brevity.
I would be grateful for any help in getting this right as I'm stuck now.
Many thanks
Kewa
--------------------------------
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Sheet2")
Set rng = ws.Range("C1:C100")
Sheets("Sheet1").Select
On Error Resume Next
For Each cell In rng
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hrimes.com/" & rng & ".dat",
Destination:=Range("B2:B" & Range("C65536").End(xlUp).Row+1)
.Name = rng
<SNIP>
End With
Next
On Error GoTo 0
End Sub
----------------
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hrimes.com/137.dat", Destination:=Range("A1"))
.Name = "137"
.PreserveFormatting = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.Refresh BackgroundQuery:=False
End With
-----------------
However I now need to consolidate data from a range of sources so need to
amend these lines.
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hrimes.com/137.dat", Destination:=Range("A1"))
.Name = "137"
The data sources will be in a list in Sheet2 in col C. At present there are
6 of these but they could increase dramatically.
So what I'm trying to do is to get the macro to use the list in Col C of
Sheet2 to determine the specific filename at www.hrimes.com
and change the Destination from Range("A1") to the next available cell in
Col B of Sheet1 so each range follows on directly from the other.
So, I've changed the macro (please see below) and appear to have made a hash
of it. The first stage of retrieving the data isn't working and therefore I
haven't been able to test the destination code either. I've snipped the
lines that are the same for brevity.
I would be grateful for any help in getting this right as I'm stuck now.
Many thanks
Kewa
--------------------------------
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Sheet2")
Set rng = ws.Range("C1:C100")
Sheets("Sheet1").Select
On Error Resume Next
For Each cell In rng
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hrimes.com/" & rng & ".dat",
Destination:=Range("B2:B" & Range("C65536").End(xlUp).Row+1)
.Name = rng
<SNIP>
End With
Next
On Error GoTo 0
End Sub