dimanche 31 juillet 2016

Issues with Web Query from Multiple Pages

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.

Code:

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://ift.tt/2aTmAFK"
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://ift.tt/2a8NXOH

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.




Aucun commentaire:

Enregistrer un commentaire