Hi
I'm new to VBA and I've written a web scraper that returns the tables from multiple pages. The macro loops through each page and returns about 50 records from each page and then moves to the next page. There are about 967 pages. The macro runs all the way through without raising and error but on review of the information, the first 33434 rows are returned correctly but after 33434 the URL is missing from the last column. I need the URL as I have a second macro which then navigates to each of these URL's.
I've checked that the records that appear past row 33434 do have URL's on the site and they do. I've also changed my variables to long in case it was an integer issue. Neither has fixed the problem. It appears to be a caused by some sort of limit or memory issue but I'm stumped and any help how to fix this would be much appreciated.
Davy
Excel 2013
[code]
Option Explicit
Sub GetPropIDs()
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.PrintCommunication = False
'Initialise Variables
Dim x As Long, lastrow As Long, y As Long, b As Long
'Delete All Content
Columns("a:z").EntireColumn.ClearContents
y = InputBox("Enter Nr. of Pages", "Nr. Of Pages", 1)
For x = 1 To y
lastrow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://propertypriceregisterireland.com/?action=search&county=6&date_from=2010-01-01&date_to=" & Date & "&price_from=0&price_to=0&property_type=0&address=&page=" & x _
, Destination:=Range("A" & lastrow))
.Name = _
"?action=search&county=6&date_from=2010-01-01&date_to=2015-03-15&price_from=0&price_to=0&property_type=0&address=&page=" & x
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.PrintCommunication = True
lastrow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
Range("f1") = "Further Details"
Range("g1") = "URL"
Next x
ActiveWorkbook.Save
End Sub
[code]
Bookmarks