scrape webpage code problem

[muppet77]

i have this code that i once got help to build a while back and it worked then.

basically you put a webpage addresses in column A and it imports the webapages and then uses tables "1" and "3" to cut data into a new sheet.

it runs until it gets to the end of column A and so has scraped all webpage addresses i have entered into column A.

unforunately now it does a couple (or sometimes none) then crashes.

runtime error 1004 - something about a file not being there?

Sub Macro2()
a = 1
Sheets(3).Select
While Sheets(1).Cells(a, 1) <> ""
urladdress = "URL;" & Sheets(1).Cells(a, 1).Text

With ActiveSheet.QueryTables.Add(Connection:= _
urladdress, Destination:=Range( _
"$A$1"))
.Name = Right(Sheets(1).Cells(a, 1).Value, 42)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("b4").Copy Sheets(2).Cells(a, 1)
Range("b8").Copy Sheets(2).Cells(a, 2)
Range("c8").Copy Sheets(2).Cells(a, 3)
Range("a11").Copy Sheets(2).Cells(a, 4)
Range("b11").Copy Sheets(2).Cells(a, 5)
Range("c11").Copy Sheets(2).Cells(a, 6)
a = a + 1
Sheets(3).Cells.ClearContents
Wend
End Sub


example webpages i paste into a1, a2, a2

http://soccernet.espn.go.com/match?id=237789&league=USA.1&cc=5739
http://soccernet.espn.go.com/match?id=237790&league=USA.1&cc=5739
http://soccernet.espn.go.com/match?id=237791&league=USA.1&cc=5739


(basically i am trying to get MLS data into excel - don't ask)

can anyone help?

 

[muppet77]

Sorry norie. Yes the web addresses are structured identically as are the pages and yes the data that you posted is precisely what i would like. Date. Team. Team. Corners. Number. Number.

 

[Norie]

And did you try the code.

 

[muppet77]

yes it worked a treat. thanks Norie!

top man.

 

[abhitha]

with regards to the subject, i too have a doubt. I wanted to extract the details from links and i tried with this query. do help me out please

Sub Macro2()
a = 1
Sheets(3).Select
While Sheets(1).Cells(a, 1) <> ""
urladdress = "URL;" & Sheets(1).Cells(a, 1).Text

With ActiveSheet.QueryTables.Add(Connection:= _
urladdress, Destination:=Range( _
"$A$1"))
.Name = Right(Sheets(1).Cells(a, 1).Value, 80)
.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
'.WebTables = "1,""wedstrijdTable2"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("a99").Copy Sheets(2).Cells(a,1)
Range("a100").Copy Sheets(2).Cells(a,2)
Range("a101").Copy Sheets(2).Cells(a,3)
Range("a102").Copy Sheets(2).Cells(a,4)
Range("a103").Copy Sheets(2).Cells(a,5)
Range("a104").Copy Sheets(2).Cells(a,6)
Range("a105").Copy Sheets(2).Cells(a,7)
Range("a106").Copy Sheets(2).Cells(a,8)
Range("a107").Copy Sheets(2).Cells(a,9)
a = a + 1
Sheets(3).Cells.ClearContents
Wend
End Sub

 

[Norie]

This thread is almost 5 years old, probably better to start a new thread.