paste data from web to next available row
 

macro1
go to website http://abc.....com
copy data table from website.
paste in sheet 1
once sheet is full (65k rows), paste on next sheet (which will be
sheet2, sheet3, etc)

** each download may be up to 7,000 - 8,000 rows. I am going to run a
query from access to store this info. I had a web query performing
this on separate sheets initially, but need to consolidate in ms
access.

paste data from web to next available row
 

The url you gave goes nowhere so it is difficult to have an idea of what you
want.
I would probably use a macro to establish an external query and then refresh
as desired and copy data from the import sheet to wherever desired. I often
do this for clients.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"J.W. Aldridge" wrote in message
...
macro1
go to website http://abc.....com
copy data table from website.
paste in sheet 1
once sheet is full (65k rows), paste on next sheet (which will be
sheet2, sheet3, etc)

** each download may be up to 7,000 - 8,000 rows. I am going to run a
query from access to store this info. I had a web query performing
this on separate sheets initially, but need to consolidate in ms
access.

paste data from web to next available row
 

Found this one here that works for me...
(website on secure server so replaced it here with "x")

Just need to ammend to:
Paste to next available row or next sheet if there is no more space
available.


Sub test()
Dim ipstring As String
Application.DisplayAlerts = False
ipstring = "x"

With ActiveSheet.QueryTables.Add(Connection:="URL;" _
& ipstring, Destination:=Range("a1"))
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range(Range("a1"), Range("a1").End(xlDown)).Select

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Columns.AutoFit
Range("a1").Select

Application.DisplayAlerts = True
End Sub

paste data from web to next available row
 

With a macro
You would need to determine how many blank rows are available on existing
sheet.
You would need to know how many rows you are to copy
If not enough, you would need to create another sheet and use that for your
paste using index.

I also recommend that you establish your query and then refresh with
variables or, at least, delete the build up of external names created.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"J.W. Aldridge" wrote in message
...
Found this one here that works for me...
(website on secure server so replaced it here with "x")

Just need to ammend to:
Paste to next available row or next sheet if there is no more space
available.


Sub test()
Dim ipstring As String
Application.DisplayAlerts = False
ipstring = "x"

With ActiveSheet.QueryTables.Add(Connection:="URL;" _
& ipstring, Destination:=Range("a1"))
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range(Range("a1"), Range("a1").End(xlDown)).Select

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Columns.AutoFit
Range("a1").Select

Application.DisplayAlerts = True
End Sub