Import Web Data - Repeat macro

[leeksleeks]

Hi,

A former colleague of mine wrote the code below and has now left. I am wanting to modify the code so that it can repeat the macro over and over again. The limitations of this is that you have to go into each webpage one by one and paste it in, wait for the macro to run and then repeat the process over again for the next page. What I would like it to do is to ask for the number on the end of the webadress to be put into the popup box, and then ask for another pop up box to ask the number of the last webpage and to then work its way through from the first one to the last one.

This is what I mean for the webpages:

http://www.espncricinfo.com/west-indies-v-pakistan-2013/engine/match/645645.html
http://www.espncricinfo.com/west-indies-v-pakistan-2013/engine/match/645647.html

So I would like the first popup box to ask for the first code (654645) and then the next one to ask for the last webpage (645647) and so then the macro runs for 654645 and then 645646 and finally 645647.

Is there anyway you can make the url =http://www.espncricinfo.com/west-indies-v-pakistan-2013/engine/match/ and then replace the 645645 with 'i' that would then refer to the pop up boxs?

Below is the code that he wrote.

Please let me know if you need any more information.

Cheers


Sub one()


'Create new Scorecard sheet
Dim url As String
Dim matchtype As String
url = "URL;" & "" & InputBox("Please enter Scorecard URL") & ""
matchtype = InputBox("Enter match type (International = I, Domestic = D)")


ActiveWorkbook.Worksheets.Add.Name = "Scorecard"
With ActiveSheet.QueryTables.Add(Connection:= _
url, Destination _
:=Range("A1"))
.Name = "455234"
.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
.Refresh BackgroundQuery:=False
End With




'Update url to look at overs comparison
url = url & "?view=comparison"

'Create new Comparison sheet
ActiveWorkbook.Worksheets.Add.Name = "Comparison"
With ActiveSheet.QueryTables.Add(Connection:= _
url, Destination _
:=Range("A1"))
.Name = "455234"
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.Refresh BackgroundQuery:=False
End With

End Sub