Excel Web查询动态数据 [英] Excel web query for dynamic data

问题描述

我试图将抵押贷款利率从银行网站提取到Excel 2010中。这是适用的网站： http://www.bmo.com/home/personal/banking/rates/mortgages

I am trying to pull mortgage rates from a bank website into Excel 2010. This is the applicable website: http://www.bmo.com/home/personal/banking/rates/mortgages

我已经创建了一个网络查询Excel这样做：（数据 - >从Web - >网站）。浏览浏览器，页面显示实际费率。在Excel中，我只收到这样的占位符：

I have created a web query in Excel to do this: (Data --> From Web --> "web site"). Looking through a browser, the page displays actual rates. In Excel, I only get placeholders like this:

Mortgage Rates  
5 year Low Rate (closed)   %*
Prime Rate                 %

问题似乎是Excel不能识别动态内容网页。如果我对感兴趣的数据查看来源，我看到这些费率由JavaScript变量填充：

The problem seems to be that Excel does not recognize the dynamic content on the webpage. If I "view source" for the data of interest, I see that the rates are populated by javascript variables:

<td class="ratesSubLabel">Prime Rate</td>
<td class="rate"><script type="text/javascript">document.write(prime.value);</script>%</td>

有人可以帮助吗？

推荐答案

我从来没有真正使用过Web源类型的东西（从来没有像我想要的那样）。

I've never really used the web source type thing before (it never worked how I wanted).

你可以使用VBA将数据从页面中删除到Excel中。

You can use VBA to scrape the data from the page into Excel.

以下内容应从站点上的表中读取数据，并将结果打印到sheet1中。

The following should read the data from the table on the site and print the results into sheet1.

' The following code should be put in a new code module
' Requires "Microsoft Internet Controls" and "Microsoft HTML Object Library" References
Sub Main()
Dim Browser As InternetExplorer
Dim Document As HTMLDocument
Dim Element As IHTMLElement
Dim Elements As IHTMLElementCollection
Dim Child As IHTMLElement
Dim Children  As IHTMLElementCollection
Dim Row As Integer
Dim Column As Integer
Row = 1
Set Browser = New InternetExplorer
Browser.Navigate "www.bmo.com/home/personal/banking/rates/mortgages"
Do While Browser.Busy And Not Browser.ReadyState = READYSTATE_COMPLETE
    DoEvents
Loop
Set Document = Browser.Document
Set Elements = Document.getElementsByTagName("table")(0).getElementsByTagName("tr")
Debug.Print Elements.Length
For Each Element In Elements
    Column = 1
    'Dim Children As IHTMLElementCollection
    'Dim Child As IHTMLElement
    Set Children = Element.getElementsByTagName("td")
    For Each Child In Children
        ThisWorkbook.Worksheets("Sheet1").Cells(Row, Column).Value = Child.innerText
        Column = Column + 1
    Next Child
    Row = Row + 1
Next Element
End Sub

这篇关于Excel Web查询动态数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！