从zoopla.co.uk获取数据 [英] Fetch data from zoopla.co.uk

问题描述

我正在尝试创建一个查看电子表格中单元格的函数，并从页面 http://www.zoopla.co.uk/market/返回Zoopla'Z-Index'英国/ 。请注意，URL内的 市场 部分是唯一可以更改的位，例如BS8，BS6等，这应该从选定的单元格中取出从我的电子表格中。然后应该返回Z-Index值，它是All，Detached，Semi和Terrace的平均物业价格。

这是迄今为止所创建的。

我写的代码没有从单元格A1拉出邮政编码的平均属性数字。任何想法？

  Private Sub Worksheet_Change（ByVal Target As Range）
如果Target.Row = Range。（L ：L）。Row And _ 
 Target.Column = Range。（L：L）。Column Then 
 Dim IE As New InternetExplorer 
 IE.Visible = False 
 IE.navigatewww.zoopla.co.uk/market/&范围（A1 /）。值
 Do 
 DoEvents 
循环直到IE.readyState = READYSTATE_COMPLETE 
 Dim Doc As HTMLDocument 
设置Doc = IE.document 
 Dim sSPAN As String 
 sSPAN = Trim（Doc.getElementsByTagName（span）（1）.innerText）
 sSPAN =拆分（sSPAN，vbNewLine）（0）
范围（ O = O）Value = Split（sSPAN，，）（0）
 Range（P：P）Value = Split（sSPAN，，）（1）
 End如果
 End Sub！[需要数据] [1] 
  

解决方案>

您的脚本有一些错误，我已经更正了一些行，它的作用是：

  Private Sub Worksheet_Change（ByVal Target作为范围）
 Const READYSTATE_COMPLETE = 4 
 
 Dim j 
 Dim xcolElements 
 Dim Doc，el，IE 
 
'
 '如果单元格L1的内容改变：
'
如果Target.Row = Range（L1）。Row和_ 
 Target.Column = Range（L1）。列然后
 
设置IE = CreateObject（InternetExpl orer.Application）
 IE.Visible = False 
 IE.Navigatehttp://www.zoopla.co.uk/market/&范围（A1）。 /
 Do 
 DoEvents 
循环直到不是IE.Busy和IE.ReadyState = READYSTATE_COMPLETE 
 
设置Doc = IE.Document 
 
设置xcolElements = Doc.getElementsByClassName（zoopla）
'
j = 1 
对于每个el在xcolElements 
 Range（O& j）.Value = el。 FirstChild.Data 
j = j + 1 
下一个
'
 IE.Quit 
'
 End If 
'
'destruct对象：
'
设置el = Nothing 
设置xcolElements = Nothing 
设置Doc = Nothing 
设置IE = Nothing 
'
 End Sub 
  

你知道VBA + IE是如此强大，您可以通过调整上述VBA代码来做许多事情。

我们使用

 设置IE = CreateObject（InternetExplorer.Application）
  

而不是

 设置IE = new InternetExplorer.Application 
  

以避免添加InternetExplorer.Application ActiveX引用，在打开没有IE自动化使用的文档时，最小化RAM要求
。

 '
'填充C12，C13，C14，... 
'
j = 12 
对于每个el在xcolElements 
范围（C &安培; j）.Value = el.FirstChild.Data 
 j = j + 1 
下一个
  

I am trying to create a function which looks at a cell in my spreadsheet and returns the Zoopla 'Z-Index' from the page http://www.zoopla.co.uk/market/uk/. Note the market section from within the URL is the only bit that changes for example BS8, BS6 etc. and this should be taken from a selected cell from within my spreadsheet. It should then return the Z-Index value which is the average property price for All, Detached, Semi and Terrace.

This is what I have created so far.

The code I have written is not pulling the average property figure for the post code from cell A1. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range.("L:L").Row And _
   Target.Column = Range.("L:L").Column Then 
    Dim IE As New InternetExplorer
    IE.Visible = False
    IE.navigate "www.zoopla.co.uk/market/" & Range("A1/").Value
    Do
        DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE
    Dim Doc As HTMLDocument
    Set Doc = IE.document
    Dim sSPAN As String
    sSPAN = Trim(Doc.getElementsByTagName("span")(1).innerText)
    sSPAN = Split(sSPAN, vbNewLine)(0)
    Range("O:O").Value = Split(sSPAN,", ")(0)
    Range("P:P").Value = Split(sSPAN,", ")(1)
End If
End Sub![Data needed][1]

解决方案

Your script has some errors, I've corrected some lines, it works:

Private Sub Worksheet_Change(ByVal Target As Range)
  Const READYSTATE_COMPLETE = 4

  Dim j
  Dim xcolElements
  Dim Doc, el, IE

  '
  ' if the content of the cell L1 changes:
  '
  If Target.Row = Range("L1").Row And _
     Target.Column = Range("L1").Column Then

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False
    IE.Navigate "http://www.zoopla.co.uk/market/" & Range("A1").Value & "/"
    Do
      DoEvents
    Loop Until Not IE.Busy And IE.ReadyState = READYSTATE_COMPLETE

    Set Doc = IE.Document

    Set xcolElements = Doc.getElementsByClassName("zoopla")
    '
    j = 1
    For Each el In xcolElements
      Range("O" & j).Value = el.FirstChild.Data
      j = j + 1
    Next
    '
    IE.Quit
    '
  End If
  '
  ' destruct objects:
  '
  Set el = Nothing
  Set xcolElements = Nothing
  Set Doc = Nothing
  Set IE = Nothing
  '
End Sub

You know that VBA+IE is so powerful that you can do many things by adapting the above VBA code.

We use

Set IE = CreateObject("InternetExplorer.Application")

Instead of

Set IE = new InternetExplorer.Application

to avoid adding InternetExplorer.Application ActiveX references, minimizing RAM requirement when opening a document without IE automation usage.

'
' to fill C12, C13, C14,...
'
j = 12
For Each el In xcolElements
  Range("C" & j).Value = el.FirstChild.Data
  j = j + 1
Next

这篇关于从zoopla.co.uk获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！