Making a macro loop

[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

 

[Guest]

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

 

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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

 

[Guest]

That's sorted out the data retrieval. Thanks a lot John.

My only outstanding problem for now is I was expecting each set of data to
go into the cell below the last populated cell in Sheet1 Col B but I've got
data from 3 files running and they go into Cols B, C and D respectively.

I thought my code... Destination:=Range("B2:B" &
Range("B65536").End(xlUp).Row+1) would do this but I must be doing something
wrong.

Any ideas would be much appreciated

 

[Guest]

Sorry Don it is a made up site for illustrative purposes only rather than
using my work site.

From the messages above I've managed to get the data extracted but am
struggling with the destination as the data is coming back in consecutive
columns rather than in blocks under each other in Col B.

Thanks a lot

 

[Don Guillett]

One way is to delete the columns>do the fetch>copy elsewhere>
do it again