Making a macro loop
 

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

Making a macro loop
 

Try this code

Sub test()

With Sheets("Sheet2")

LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
Set HyperRange = Range(.Cells(1, "C"), .Cells(LastRow, "C"))
End With

RowCount = 1
For Each cell In HyperRange

With ActiveSheet.QueryTables.Add(Connection:= _
cell.Value, Destination:=Range("A" & RowCount))

.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

RowCount = RowCount + 1
Next cell

End Sub


"nospaminlich" wrote:

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

Making a macro loop
 

For this first part it looks like your problem is in your string
"URL;http://www.hrimes.com/" & rng & ".dat",
you are trying to set the filename to an entire range of filenames, since
you are doing each cell in rng it would be
"URL;http://www.hrimes.com/" & cell & ".dat",
that ought to get you started
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"nospaminlich" wrote:

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

Making a macro loop
 

I could not get the hrimes.com website to come up. Send me a workbook if you
like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"nospaminlich" wrote in message
...
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

Making a macro loop
 

Thanks Joel

Tried this but I just get a 400 error. Not sure if this has anything to do
with me using Excel 2000.

Also I might be being stupid here but the data coming back needs to go to
the first blank cell in Col B of Sheet1 and I don't understand how this is
represented in your code.

Kewa