+ Reply to Thread
Results 1 to 2 of 2

Multiple page Excel web query loses data at row 33435

  1. #1
    Registered User
    Join Date
    03-14-2013
    Location
    Oz
    MS-Off Ver
    Excel 2013
    Posts
    4

    Multiple page Excel web query loses data at row 33435

    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

    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
    Last edited by DAVYAMADAOS; 03-20-2015 at 04:22 AM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166
    Hello DAVYAMADAOS,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to modify a VBA to place all data on one page instead of a new page each query
    By PistachioPedro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-17-2013, 01:56 PM
  2. Data transferred into Excel loses color formatting
    By Sleem0711 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-27-2013, 09:09 PM
  3. [SOLVED] Web query to download web page data to excel in text format
    By panditji in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2012, 12:30 AM
  4. Replies: 1
    Last Post: 02-19-2006, 02:55 PM
  5. Excel loses email field data when upgrading from XP to 2003
    By Jeff McBride in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-06-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1