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?
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?