Issues with Dynamic Web Query from Multiple Web Pages

[baillta]

Hi,

I was wondering if someone may be able to help me. I am trying to write a code that extracts House Sale data based on user defined search inputs. These inputs are the Suburb (B2) and the State of the property (C2). The problem is the search produces 10 results per page however I am after the first 100 search results (the first 10 pages).

I recorded a macro using the web query function and modified it slightly (see below) which draws the first page of data without issues.

Sub HousePrices()


    Dim wb As Workbook
    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim url As String
    Dim suburb As String
    Dim state As String
    
    Set wb = ThisWorkbook
    Set src = wb.Sheets("Sheet1")


    suburb = src.Range("B2")
    state = src.Range("C2")
    url = "URL;http://house.ksou.cn/p.php?q="
    url = url & suburb & "%2C+" & state


Sheets.Add.Name = suburb
 
    Set tgt = wb.Sheets(suburb)


    With tgt.QueryTables.Add(Connection:= _
        url, Destination:=tgt.Range("$A$1"))
        .Name = "Page1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "11,15,19,23,27,31,35,39,43,47"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

End Sub

The problem is when I try to extract data from subsequent pages the URL changes to something like this (dynamic elements of URL in bold):

http://house.ksou.cn/p.php?q=Suburb&p=1&s=1&st=&type=&count=300&region=Suburb&lat=0&lng=0&sta=State&htype=&agent=0&minprice=0&maxprice=0&minbed=0&maxbed=0

If anybody could help me with this it would be greatly appreciated. I want the destination of info from subsequent pages to be dumped below the previous page in the same sheet.

Thanks in advance.

 

[baillta]

Anyone? Any ideas?

 

[baillta]

Still hoping I can get some help regarding this issue. I have inserted a loop which should theoretically download the first 10 pages however while the subroutine compiles okay it doesn't output anything.

 

[baillta]

Hi Guys, didn't end up getting any hits on this question so thought I'd bump it again to see if anyone has any new ideas?

Thanks in advance