help about my code (get data)

[Ethen5155]

Hi all,

kindly i need some help about my below code to get data from web, i want to make it go through multiple ID number and get data

for ex,

the web link is: http://www.vbaexpress.com/kb/getarticle.php?kb_id=50

i want it to get through id=50 to be like from 50 to 1000


http://www.vbaexpress.com/kb/getarticle.php?kb_id=51
http://www.vbaexpress.com/kb/getarticle.php?kb_id=52
http://www.vbaexpress.com/kb/getarticle.php?kb_id=53
http://www.vbaexpress.com/kb/getarticle.php?kb_id=54
http://www.vbaexpress.com/kb/getarticle.php?kb_id=55
http://www.vbaexpress.com/kb/getarticle.php?kb_id=999

.........etc



my code:


Sub FetchData()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.vbaexpress.com/kb/getarticle.php?kb_id=50", Destination:=Range( _
"$A$1"))

.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


Thanks in advance

cheers

Ethen

Cross-Posting: http://www.excelforum.com/showthread.php?t=1172313&p=4574042#post4574042

[p45cal]

Sub FetchData()
For i = 50 To 55
Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.vbaexpress.com/kb/getarticle.php?kb_id=" & i, Destination:=Range("$A$1"))
.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next i
End Sub

[Ethen5155]

Dear p45cal.

thx for your reply, well your code works well but i deleted (Sheets.Add) line to get all data in first tab sheet only

but i have another issue

i need all data to be on column (A) and (B) only

please test that code and see the result to get what i mean



Sub Data() For i = 50 To 55

With ActiveSheet.QueryTables.Add(Connection:="URL;http://basm.kacst.edu.sa/ViewTerm.aspx?termid=" & i, Destination:=Range("$A$1"))
.Name = "Test"
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next i
End Sub





Cheers

Ethen

[p45cal]

Sub FetchData()
Set Destn = Range("A1")
For i = 50 To 55
With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.vbaexpress.com/kb/getarticle.php?kb_id=" & i, Destination:=Destn)
.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
Set Destn = Destn.Offset(.ResultRange.Rows.Count)
End With
Next i
End Sub