从网站检索数字到Excel [英] Retrieve Number from a website into Excel

问题描述

从本网站 http://bit.ly/1Ib8IhP

我试图将这个数字转成Excel单元格。

I am trying to get this number into an Excel cell.

Avg. asking price in Bayswater Road:  £1,828,502

有没有办法使用VBA或其他工具？

Is there any way using VBA or another tool? Couldn't make it work with a web query.

推荐答案

这是一种可能的解决方案：

Here is one of the possible solutions:

Option Explicit

Sub RetrieveAvgPrice()
    Dim sUrl, sContent, sPrice
    sUrl = "http://www.zoopla.co.uk/home-values/london/bayswater-road/"
    With CreateObject("MSXML2.XMLHttp")
        .Open "GET", sUrl, False
        .Send ""
        sContent = .ResponseText
    End With
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "Avg\. asking price[\s\S]*?class=""price big"">([\s\S]*?)<"
        sPrice = HtmlSpecialCharsDecode(.Execute(sContent).Item(0).SubMatches(0))
    End With
    MsgBox sPrice
End Sub

Function HtmlSpecialCharsDecode(sText)
    With CreateObject("htmlfile")
        .Open
        With .createElement("textarea")
            .innerHTML = sText
            HtmlSpecialCharsDecode = .Value
        End With
    End With
End Function

这篇关于从网站检索数字到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！