Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a macro always available | Excel Discussion (Misc queries) | |||
Do until loop with use of another macro in loop | Excel Programming | |||
MACRO, help for making | Excel Programming | |||
Making macros loop | Excel Programming | |||
Need Help on Making a Loop | Excel Programming |