+ Reply to Thread
Results 1 to 8 of 8

Multiple Page Web Query Loses Data at line 33434

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

    Multiple Page Web Query Loses Data at line 33434

    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]

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Multiple Page Web Query Loses Data at line 33434

    I have not tested all the pages, but it appears that you are missing one row at a time as your code looks only at the last row, not the next row after the last row.
    Change this line

    Please Login or Register  to view this content.

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

    Re: Multiple Page Web Query Loses Data at line 33434

    Thanks for the "row+1" it brought in some extra records i'd missed.
    Unfortunately I still have the same problem. It populates the last column with "Further Details" but after row 33422 (now due to the row+1 change) it brings in the text "Further Details" but loses the URL connected with it.

    I'm running Excel 2013 64bit. Could it be connected with not clearing the memory cache after each loop or using longPtr??

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Multiple Page Web Query Loses Data at line 33434

    I have run the code and has got the same number of columns. Yes, the layout of the columns are not the same for each table and you can see the out put after row 34421 is different. You have not control on the content of the table-it could be 6 columns, or 7, i.e. all tables might not have the same column size.
    I suggest you put the header in after the end of the loop if G1 and F1 are headers in deed. Let all the tables are copied first and then you can add the headers.

    Please Login or Register  to view this content.
    Last edited by AB33; 03-24-2015 at 12:17 PM.

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

    Re: Multiple Page Web Query Loses Data at line 33434

    Thanks, that is a lot neater. Unfortunately I still have the same problem. The table structure is the same on every web page. The code doesn't miss populating a column and returns the "friendly name" and the associated "hyperlink" in column F. It does this consistently from row 1 to 33422, but from row 33422 onward, the "friendly name" is inserted but the associated "hyperlink" is missing.

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Multiple Page Web Query Loses Data at line 33434

    I do not know what is missing. My guess is the data after row 34k are not properly formatted. I have changed the formats of the cells and removed the text wrap. I have not looked at the site yet, but some tables might have col and row span which makes them the cells to appear as wrapped.
    Attached Files Attached Files

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

    Re: Multiple Page Web Query Loses Data at line 33434

    I ran your spreadsheet and got 33421 before it dropped the URL from column F. Your run appeared to get a few more.
    I tried adding "ActiveWorkbook.Connections("Connection").Delete" at the end of each loop but it still didn't help.
    I also ran the script from page 900 through to the 967 to capture a sample of just the data which previously lost the URL. This worked so it doesn't appear to be the way the data is stored on the site. Stumped.....

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Multiple Page Web Query Loses Data at line 33434

    No, I have already run the code as you can see on the output. You need to show me what is missing from the output data.

+ 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. Multiple page Excel web query loses data at row 33435
    By DAVYAMADAOS in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 03-20-2015, 03:53 AM
  2. Replies: 0
    Last Post: 02-19-2014, 03:49 PM
  3. 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
  4. [SOLVED] [SOLVED] Move data line from end of page 1 to page 2
    By sweeta1974 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-19-2006, 09:45 AM
  5. Replies: 1
    Last Post: 02-19-2006, 02:55 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